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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.