There are two procedures commonly used for reporting purpose in SAS, PROC Report and PROC Tabulate. In this post, I will focus more of PROC Report because it is more convenient to produce column percentage by group and the sub-total. The references of column in the computation of percentages are different between a cross-tab report and the regular one dimensional report.
1. Cross-tab report design
Source table layout (Varn will be any other categorical variable that not contribute to the calculation of the percentage)
| Variables | Type |
| Year | Ordinal |
| Var1 | Categorical/Nominal |
| Var2 | Ordinal |
| Var3 | Continous |
Desired Report Output
| Year | ||||||||
| Year1 | Year2 | Year3 | ||||||
| Var1 | Varn | Var2 | sum of Var3 | % of sum by Var1 | sum of Var3 | % of sum by Var1 | sum of Var3 | % of sum by Var1 |
| xxxxx | xxxxx | xxxxx | xxx | xx% | xxx | xx% | xxx | xx% |
| xxxxx | xxx | xx% | xxx | xx% | xxx | xx% | ||
| xxxxx | xxx | xx% | xxx | xx% | xxx | xx% | ||
| sub-total | xxx | 100% | xxx | 100% | xxx | 100% | ||
| xxxxx | xxxxx | xxxxx | xxx | xx% | xxx | xx% | xxx | xx% |
| xxxxx | xxx | xx% | xxx | xx% | xxx | xx% | ||
| xxxxx | xxx | xx% | xxx | xx% | xxx | xx% | ||
| sub-total | xxx | 100% | xxx | 100% | xxx | 100% | ||
| xxxxx | xxxxx | xxxxx | xxx | xx% | xxx | xx% | xxx | xx% |
| xxxxx | xxx | xx% | xxx | xx% | xxx | xx% | ||
| xxxxx | xxx | xx% | xxx | xx% | xxx | xx% | ||
| sub-total | xxx | 100% | xxx | 100% | xxx | 100% | ||
Code
Proc report data = sample nowd ; column var1 varn var2 year,(var3 ptvar3); define var1 / group; define varn / group; define var2 /group; define year / across 'Year'; define var3 / sum f=7.2 'SUM'; define ptvar3 /computed f=percent8.2 '%'; /* Sum total var3 by year */ Break after var1 / summarize; compute before var1; den0 = _c4_; den1 = _c6_; den2 = _c8_; endcomp; /* Calculate percentage */ compute ptvar3; _c5_ = _c4_ / den0; _c7_ = _c6_ / den1; _c9_ = _c8_ / den2; endcomp; run;
2. One dimensional report
Source table layout
| Variables | Type |
| Var1 | Categorical/Nominal |
| Var2 | Ordinal |
| Var3 | Continous |
Desired Report Output
| Var1 | Varn | Var2 | sum of Var3 | % of sum by Var1 |
| xxxxx | xxxxx | xxxxx | xxx | xx% |
| xxxxx | xxx | xx% | ||
| xxxxx | xxx | xx% | ||
| sub-total | xxx | 100% | ||
| xxxxx | xxxxx | xxxxx | xxx | xx% |
| xxxxx | xxx | xx% | ||
| xxxxx | xxx | xx% | ||
| sub-total | xxx | 100% | ||
| xxxxx | xxxxx | xxxxx | xxx | xx% |
| xxxxx | xxx | xx% | ||
| xxxxx | xxx | xx% | ||
| sub-total | xxx | 100% |
Code
Proc report data = sample nowd ; column var1 varn var2 var3 ptvar3; define var1 / group; define varn / group; define var2 /group; define var3 / sum f=7.2 'Var3'; define ptvar3 /computed f=percent8.2 '%'; Break after var1 / summarize; compute before var1; var3den = var3.sum; endcomp; /* Calculate percentage */ compute ptvar3; ptvar3 = var3.sum / var3den; endcomp; run;
Note:
- for cross-tab report, in column statement use “year,”. There is a ‘,’ after the variable.
- if there is more than one variable in the value section of the cross-tab report, use ‘( )’ to include all the value variables needed in the column statement. eg. ‘year, (var3 ptvar3)’
- use break statement to provide sub-totals for the report;
- for computing the percentage by group, denX variables are used to provide sum value of the group and _cX_ variables are used to hold all results for the column value. The column number X in _cX_ needs to be matched with the exact column number in the output.
- for computing percentage for one dimensional report, refer to the variable name directly (eg. ptvar3 = var3.sum/ var3den) in the formula. Refer to the column number will not work in this case.
reference: