topic Re: Question on proc report about summarizing data in SAS Procedures

Question on proc report about summarizing data

Inp — Sat, 25 Sep 2010 02:08:57 GMT

Hi Dear,
Here is my coding:
data temp;
input r $2. x $1. y $2. z 1.;
cards;
r1aa11
r1aa21
r1ba11
r1ba21
r2ca11
r2ca22
r2da11
r2da22
r3ee11
;
run;
proc report data=temp nowd nocenter;
column r x y z;
define r / group;
define x / group;
define y / 'y';
define z / analysis;

break after x/summarize DOL DUL ;
break after r /summarize DOL DUL ;
rbreak after / summarize DOL DUL ;
run;

How do I get the expeced below output:

r x y z
r1 a a1 1
a2 1
== = =========
r1 a 2
== = =========
b a1 1
a2 1
== = =========
r1 b 2
== = =========
== =========
r1 4
== =========
r2 c a1 1
a2 2
== = =========
r2 c 3
== = =========
d a1 1
a2 2
== = =========
r2 d 3
== = =========
== =========
r2 6
== =========
r3 e e1 1
== = =========
=========
11
=========

but I am getting the below

r x y z
r1 a a1 1
a2 1
== = =========
r1 a 2
== = =========
b a1 1
a2 1
== = =========
r1 b 2
== = =========
== =========
r1 4
== =========
r2 c a1 1
a2 2
== = =========
r2 c 3
== = =========
d a1 1
a2 2
== = =========
r2 d 3
== = =========
== =========
r2 6
== =========
r3 e e1 1
== = =========
r3 e 1
== = =========
== =========
r3 1
== =========
=========
11
=========

I don't want repeat r3 three times. I need this to appear only one time in the report
Thanks so much for help in advance.

Thanks

Inp

Re: Question on proc report about summarizing data

Cynthia_sas — Sat, 25 Sep 2010 14:39:25 GMT

Hi
Even though R3 only has one row you have asked for multiple summaries with the BREAK statement for X and R. PROC REPORT summarizes EVERY group, no matter whether there is one observation in a group or 1000 observations in a group ( as described in the doc)
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473623.htm

Are you seeing any warning messages in the Log???

What is the usage of Y variable??? DISPLAY, ORDER or GROUP??

You may want PROC report to perform processing that it is unable to perform.

Cynthia

Re: Question on proc report about summarizing data

Cynthia_sas — Sun, 26 Sep 2010 18:38:19 GMT

Hi:
A followup...PROC REPORT is doing exactly what you tell it to do. If you compare the output for R=R1 versus R=R3, you will see that you are getting exactly the same output for both groups. PROC REPORT does not care whether the group has 1 detail row or 1000 detail rows. See the annotated output below:
[pre]
r x y z
r1 a a1 1 <-- detail line for a1 X=a
a2 1 <-- detail line for a2 X=a
== = =========
r1 a 2 <-- break after X=a
== = =========
b a1 1 <-- detail line for a1 X=b
a2 1 <-- detail line for a2 X=b
== = =========
r1 b 2 <-- break after X=b
== = =========
== =========
r1 4 <-- break after R
== =========

r3 e e1 1 <-- detail line for e1 X=e
== = =========
r3 e 1 <-- break after X=e
== = =========
== =========
r3 1 <-- break after R
== =========

=========
11 <-- summary from RBREAK statement
=========
[/pre]

As you can see, when R=R1, there are detail lines and summary lines, placed by the BREAK AFTER X and BREAK AFTER R. And with R=R3, even though you only have 1 detail line, you see the detail line, followed by the same summary lines which were produced for R=R1 (or R=R2 -- R2 not shown here to save space).

PROC REPORT will not treat the group for R3 any differently than it treats the groups for R1 or R2. So, PROC REPORT is giving you the only results that it can give you -- expecting anything different from PROC REPORT is unrealistic and contrary to the way that PROC REPORT has always worked.

cynthia