Totalling in proc tabulate

[Q1983]

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

[SuryaKiran · Accepted solution]

Class prog_ctgy CODEDESC /missing;

This tells to include missing values.