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
Class prog_ctgy CODEDESC /missing;
This tells to include missing values.
Class prog_ctgy CODEDESC /missing;
This tells to include missing values.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.