data test2;
set test;
run;
Output is
| GROSS_LN_AMT |
prog_ctgy |
CODEDESC |
ln_cnt |
| $242,725.95 |
Doctor |
|
1 |
| $613,000.00 |
Doctor |
101 - Appraisal / Property |
1 |
| $224,900.00 |
Doctor |
101 - Appraisal / Property |
1 |
| $580,000.00 |
Doctor |
101 - Appraisal / Property |
1 |
| $591,805.00 |
Key |
|
1 |
| $857,500.00 |
Key |
|
1 |
| $568,000.00 |
Key |
102 - Assets |
1 |
| $764,000.00 |
Key |
102 - Assets |
1 |
| $489,025.00 |
LIBOR ARM |
|
1 |
Proc tabulate data=test2 order= data format=10. S=[cellwidth=80];
Class prog_ctgy CODEDESC;
Var GROSS_LN_AMT ln_cnt;
Table CODEDESC='' all={label='Total' S=[background = lightblue cellwidth=80]} *[STYLE=[Font_Weight=BOLD]],
prog_ctgy*(ln_cnt=''*sum=' Units' ln_cnt=''*colpctn='% of Units'*f=number8.2
GROSS_LN_AMT=''*sum='Dollars$' *f=dollar16.2 GROSS_LN_AMT='' *colpctSum='% of Dollars'*f=number8.2 )
all={ label='Grand Total' S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]]
*(ln_cnt =' '*sum=' Units' GROSS_LN_AMT =' '*sum='Total Dollars$' *f=dollar16.2
ln_cnt=''*colpctn='Total % in Units' *f=number8.2 GROSS_LN_AMT=''*colpctSum='Total % in Dollars' *f=number8.2)
/ box='Totals';
label colpctn = '% of Units';
label prog_ctgy = 'All Units';
keylabel n='';
run;
Output
| Totals |
All Units |
Grand Total |
| Doctor |
Key |
| Units |
% of Units |
Dollars$ |
% of Dollars |
Units |
% of Units |
Dollars$ |
% of Dollars |
Units |
Total Dollars$ |
Total % in Units |
Total % in Dollars |
| 101 - Appraisal / Property |
3 |
100 |
$1,417,900.00 |
100 |
|
|
|
|
3 |
$1,417,900.00 |
60 |
51.56 |
| 102 - Assets |
|
|
|
|
2 |
100 |
$1,332,000.00 |
100 |
2 |
$1,332,000.00 |
40 |
48.44 |
| Total |
3 |
100 |
$1,417,900.00 |
100 |
2 |
100 |
$1,332,000.00 |
100 |
5 |
$2,749,900.00 |
100 |
100 |
Notice that ln_cnt sums only where CodeDesc is populated. The total units is correct however I need the percentages and totals to be based on the total entries regardless of whether codedesc is populated. As example there are 3 units for Doctor however there are 4 total entries for doctor so the percentage should be 3/4=.75
