Proc Tabulate: Wrong totals and Percent Totals

[Mgarret]

Hi all—

I’m having issues getting the correct totals and percentages in Proc Tabulate tables. I can’t seem to figure out how to negotiate the code to fix the issue (my code is below the two tables).

These are the tables I'm generating now with the current vision of my code.

INDICATOR
Sub-indicator A
Q16a: Is there a recorded available during the 6 month review period?
Count	Yes		No		Count Total	% Total
Overall %
	Count	%	Count	%
Program 1	5	11%	.	.	5	11%
Program 2	8	18%	.	.	8	18%
Program 3	16	37%	1	2%	17	39%
Program 4	12	27%	1	2%	13	30%
Overall Agency Totals	41	95%	2	4%	43	100%
Q16a: Is there a recorded available?
Count	Yes		No		Skipped		Count Total	% Total
Overall %
	Count	%	Count	%	Count	%
Program 1	5	1%	.	.	.	.	5	1%
Program2	7	1%	1	0%	.	.	8	1%
Program 3	15	3%	1	0%	1	0%	17	3%
Program 4	11	2%	1	0%	1	0%	13	3%
Overall Agency Totals	38	8%	3	0%	2	0%	43	10%

Below is what I need instead. You will notice the response percentages are being calculated by dividing the Overall Agency Totals’ % Total.

INDICATOR
Sub-indicator A
Q16a: Is there a recorded available?
Count	Yes		No		Count Total	% Total
Overall %
	Count	%	Count	%
Program 1	5	11.6%	0	0.0%	5	100%
Program 2	8	18.6%	0	0.0%	8	100%
Program 3	16	37.2%	1	2.3%	17	100%
Program 4	12	27.9%	1	2.3%	13	100%
Overall Agency Totals	41	95.3%	2	4.7%	43	100%
Q17: Is the  assessment in the most recent ?
Count	Yes		No		Skipped		Count Total	% Total
Overall %
	Count	%	Count	%	Count	%
Program 1	5	11.6%	0	0.0%	0	0.0%	5	100%
Program2	7	16.3%	1	2.3%	0	0.0%	8	100%
Program 3	15	34.9%	1	2.3%	1	2.3%	17	100%
Program 4	11	25.6%	1	2.3%	1	2.3%	13	100%
Overall Agency Totals	38	88.4%	3	7.0%	2	4.7%	43	100%

Here is my code:

proc tabulate data=have

(where=(question in('Q16' 'Q17',)

& Program_Type in(&program)))

style=[background=white foreground=black  font_face='Arial Narrow' font_size=10.5pt just=c cellwidth=.6in];

title1 "^S={font_face='Arial Narrow' font_weight=bold font_size=12pt}

INDICATOR";

title2 "^S={font_face='Arial Narrow' font_weight=bold font_size=12pt}

Sub-indicator A";

class question program_name

/ style={font_face='Arial Narrow' font_weight=bold font_size=11pt cellheight=50 };

class Responce1 / order=formatted

style={font_face='Arial Narrow' font_weight=bold font_size=11pt cellwidth=.6in};

classlev question

/style=[font_face='Arial Narrow' font_size=11pt];

classlev  program_name

/style={font_face='Arial Narrow' font_size=10pt cellwidth=.6in cellheight=.4in just=l};

classlev

Responce1

/style={font_face='Arial Narrow' font_size=11pt  };

table Question=' ',

program_name =' '

all='Overall Agency Totals',

Responce1=' '*(n='Count'*f=best8. pctn='%'*f=p.)

n='Count Total' pctn='% Total' *f=p.

/style={font_face='Arial Narrow' font_size=11pt cellwidth=.6in}

row=float 

box={label='Count^{newline 1}Overall^_%'

style={font_face='Arial Narrow' font_size=10pt font_weight=bold cellwidth=.6in}};

format question $ques.;

format responce1 $resp.;

keyword n pctn  /

style=[font_weight=bold foreground=black  font_face='Arial Narrow' font_size=10pt just=c];

keyword all/ style=[font_weight=bold foreground=black  font_face='Arial Narrow' font_size=10pt just=l];

run;

The percentage format is defined with this code:

proc format ;

picture p

  low-high='0099%';

run;

Any help is greatly is greatly appreciated!!!

[Astounding]

Mgarret,

Here are a couple of items to consider.

If you want a place after the decimal point, include it in your picture format:

picture p low-high = '009.9%';

PROC TABULATE lets you specify a denominator when computing a PCTN statistic.  But it's something you should read about and play with to get a feel for.  Basically, you'll be adding your denominator definition within the TABLE statement:

pctn<denominator definition>='% Total'

To get missing values to print out as 0, I'm not sure.  There is a way to get missing values to print as 0, but that won't be good enough.  It won't print the % sign, and the 0 may not line up with other numbers in the column.  The solution may lie with a preloaded format, but that would take some experimenting and testing as well.

Sorry not to have full answers, but this will at least get you started.

Good luck.

[ballardw · Accepted solution]

One way to get your wanted display for count and percent when missing is to add missing to your P format

. = '0.0%' (noedit)

and similar for a new count format to use with n

picture c

. = '0' (noedit)

low - high = '00000009'

;

I'm not sure but I think instead of

n='Count Total' pctn='% Total' *f=p.

you may be looking for

n='Count Total' rowpctn='% Total' *f=p.

[Cynthia_sas]

Hi:

  PROC TABULATE has many ways to calculate a percentage. You hardly need to specify a denominator definition anymore with these available:

PCTN/PCTSUM

ROWPCTN/ROWPCTSUM

COLPCTN/COLPCTSUM

PAGEPCTN/PAGEPCTSUM

REPPCTN/REPPCTSUM

  In addition percents can be impacted with BY group processing. PROC TABULATE treats each BY group as a separate entity, so in some instances, BY group processing will give you what you want, as well.

  The TABULATE documentation has some good examples of using the different percent keyword statistics.

cynthia

[Mgarret]

This is exactly what I needed. Thanks!