DATA Step, Macro, Functions and more

Totalling in proc tabulate

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 141
Accepted Solution

Totalling in proc tabulate

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


Accepted Solutions
Solution
‎11-14-2016 03:01 PM
Frequent Contributor
Posts: 136

Re: Totalling in proc tabulate

Class prog_ctgy CODEDESC /missing;

 

This tells to include missing values.

Thanks,
Suryakiran

View solution in original post


All Replies
Solution
‎11-14-2016 03:01 PM
Frequent Contributor
Posts: 136

Re: Totalling in proc tabulate

Class prog_ctgy CODEDESC /missing;

 

This tells to include missing values.

Thanks,
Suryakiran
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 152 views
  • 1 like
  • 2 in conversation