Totalling in proc tabulate

Q1983 — Mon, 14 Nov 2016 19:20:29 GMT

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

100

$1,417,900.00

100

$1,417,900.00

51.56

102 - Assets

100

$1,332,000.00

100

$1,332,000.00

48.44

Total

100

$1,417,900.00

100

$1,332,000.00

100

$2,749,900.00

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

Re: Totalling in proc tabulate

SuryaKiran — Mon, 14 Nov 2016 19:44:57 GMT

Class prog_ctgy CODEDESC /missing;

This tells to include missing values.

topic Re: Totalling in proc tabulate in SAS Programming

Totalling in proc tabulate

Re: Totalling in proc tabulate