BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mgarret
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
Astounding
PROC Star

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
Super User

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
SAS Super FREQ

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
Obsidian | Level 7

This is exactly what I needed. Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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