Hi SAS experts,
Although I am using SAS for a while now, I am a newbie to PROC REPORT. I am trying to create a simple report using following data
Group | VALUE | state | Frequency | ColPercent |
A | Yes | 1 | 1 | 50 |
B | Yes | 1 | 6 | 75 |
C | Yes | 1 | 6 | 54.54545455 |
D | Yes | 1 | 10 | 52.63157895 |
E | Yes | 1 | 11 | 61.11111111 |
A | No | 1 | 1 | 50 |
B | No | 1 | 2 | 25 |
C | No | 1 | 5 | 45.45454545 |
D | No | 1 | 9 | 47.36842105 |
E | No | 1 | 7 | 38.88888889 |
A | Yes | 0 | 1 | 50 |
B | Yes | 0 | 3 | 37.5 |
C | Yes | 0 | 6 | 54.54545455 |
D | Yes | 0 | 4 | 21.05263158 |
E | Yes | 0 | 8 | 44.44444444 |
A | No | 0 | 1 | 50 |
B | No | 0 | 5 | 62.5 |
C | No | 0 | 5 | 45.45454545 |
D | No | 0 | 15 | 78.94736842 |
E | No | 0 | 10 | 55.55555556 |
I wrote following code :
proc report data=book1 ;
column group state value, (Frequency ColPercent) ;
define group / "Group" left order;
define state / "State" center ;
define value/ "Response" center across;
define Frequency / "Counts" center format=3.;
define ColPercent / "Percent" center format=5.1;
title;
run;
Procedure is producing following table
Response | |||||
No | Yes | ||||
Group | State | Counts | Percent | Counts | Percent |
A | 1 | . | . | 1 | 50 |
1 | 1 | 50 | . | . | |
0 | . | . | 1 | 50 | |
0 | 1 | 50 | . | . | |
B | 1 | . | . | 6 | 75 |
1 | 2 | 25 | . | . | |
0 | . | . | 3 | 37.5 | |
0 | 5 | 62.5 | . | . | |
C | 1 | . | . | 6 | 54.5 |
1 | 5 | 45.5 | . | . | |
0 | . | . | 6 | 54.5 | |
0 | 5 | 45.5 | . | . | |
D | 1 | . | . | 10 | 52.6 |
1 | 9 | 47.4 | . | . | |
0 | . | . | 4 | 21.1 | |
0 | 15 | 78.9 | . | . | |
E | 1 | . | . | 11 | 61.1 |
1 | 7 | 38.9 | . | . | |
0 | . | . | 8 | 44.4 | |
0 | 10 | 55.6 | . | . |
However, I want table in following format:
Response | |||||
No | Yes | ||||
Group | State | Counts | Percent | Counts | Percent |
A | 1 | 1 | 50 | 1 | 50 |
0 | 1 | 50 | 1 | 50 | |
B | 1 | 2 | 25 | 6 | 75 |
0 | 5 | 62.5 | 3 | 37.5 | |
C | 1 | 5 | 45.5 | 6 | 54.5 |
0 | 5 | 45.5 | 6 | 54.5 | |
D | 1 | 9 | 47.4 | 10 | 52.6 |
0 | 15 | 78.9 | 4 | 21.1 | |
E | 1 | 7 | 38.9 | 11 | 61.1 |
0 | 10 | 55.6 | 8 | 44.4 |
So I want value of response (count and percent) for each state in a single line. For some reason PROC report is using two lines for same group. I tried using dummy variable bu it did not work out.
I would appreciate your help.
Thanks
Hi:
To collapse rows (summarize your groups), which is what you want, change your usage of ORDER to GROUP in the DEFINE statements for GROUP and STATE -- otherwise, you are getting the default of DISPLAY for STATE and that is preventing anything from being summarized (or collapsed down to 1 row).
Again, there is a really, really good documentation topic called "How PROC REPORT Builds a Report" that describes the "side effect" of DISPLAY (the default for character), ORDER and GROUP usages.
cynthia
Hi:
To collapse rows (summarize your groups), which is what you want, change your usage of ORDER to GROUP in the DEFINE statements for GROUP and STATE -- otherwise, you are getting the default of DISPLAY for STATE and that is preventing anything from being summarized (or collapsed down to 1 row).
Again, there is a really, really good documentation topic called "How PROC REPORT Builds a Report" that describes the "side effect" of DISPLAY (the default for character), ORDER and GROUP usages.
cynthia
Thanks for your help. I will read the documentation you suggested.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.