Help using Base SAS procedures

Proc Tabulate: Wrong totals and Percent Totals

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 144
Accepted Solution

Proc Tabulate: Wrong totals and Percent Totals

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?
CountYesNoCount Total% Total
Overall %
Count%Count%
Program 1511%..511%
Program 2818%..818%
Program 31637%12%1739%
Program 41227%12%1330%
Overall Agency Totals4195%24%43100%
Q16a: Is there a recorded available?
CountYesNoSkippedCount Total% Total
Overall %
Count%Count%Count%
Program 151%....51%
Program271%10%..81%
Program 3153%10%10%173%
Program 4112%10%10%133%
Overall Agency Totals388%30%20%4310%

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?
CountYes
NoCount Total% Total
Overall %
Count%Count%
Program 1511.6%00.0%5100%
Program 2818.6%00.0%8100%
Program 31637.2%12.3%17100%
Program 41227.9%12.3%13100%
Overall Agency Totals4195.3%24.7%43100%
Q17: Is the  assessment in the most recent ?
CountYesNoSkippedCount Total% Total
Overall %
Count%Count%Count%
Program 1511.6%00.0%00.0%5100%
Program2716.3%12.3%00.0%8100%
Program 31534.9%12.3%12.3%17100%
Program 41125.6%12.3%12.3%13100%
Overall Agency Totals3888.4%37.0%24.7%43100%

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!!!


Accepted Solutions
Solution
‎09-19-2012 01:16 PM
Super User
Posts: 11,343

Re: Proc Tabulate: Wrong totals and Percent Totals

Posted in reply to Astounding

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.

View solution in original post


All Replies
Super User
Posts: 5,504

Re: Proc Tabulate: Wrong totals and Percent Totals

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.

Solution
‎09-19-2012 01:16 PM
Super User
Posts: 11,343

Re: Proc Tabulate: Wrong totals and Percent Totals

Posted in reply to Astounding

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.

SAS Super FREQ
Posts: 8,865

Re: Proc Tabulate: Wrong totals and Percent Totals

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

Frequent Contributor
Posts: 144

Re: Proc Tabulate: Wrong totals and Percent Totals

This is exactly what I needed. Thanks!

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 591 views
  • 0 likes
  • 4 in conversation