Need help with Proc report

[san134]

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

[Cynthia_sas · Accepted solution]

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

[san134]

Thanks for your help. I will read the documentation you suggested.