proc report summarize

sks — Tue, 17 May 2011 14:50:08 GMT

Hello all,
I am using proc report for one of the reports and I am not sure on how to summarize the results. I have 5 numeric fields and a flag, I would like to show a total row (summarized row) for all the numeric fields when the flag is no, but I want to only summarize 3 columns when the flag is yes

I am not sure on how to do that, if I use "display" on the numeric columns then they are never summarized, can anyone tell me is I can summarize numeric fields based on a flag.
Here is an example

flag field1 field2 field3 field4 field5
n 100 200 300 400 500
n 10 20 30 40 50
y 101 201 301 401 501
y 11 21 31 41 51

when the report runs I want the output tobe

n 110 220 330 440 550
y 331 441

Thank You
Shri

Re: proc report summarize

Cynthia_sas — Wed, 18 May 2011 00:24:03 GMT

Hi:
I am confused by your data and your results. You say that you want to summarize only 3 columns for the Y values, however, you only show 2 columns in your desired output. Also, according to my calculator, your summary numbers are "off".

For example just looking at what I think the summary should be, based on your posted data:
[pre]
Sum of N:

flag field1 field2 field3 field4 field5
n 100 200 300 400 500
n 10 20 30 40 50
110 220 330 440 550

Sum of Y:

flag field1 field2 field3 field4 field5
y 101 201 301 401 501
y 11 21 31 41 51
112 222 332 442 552
[/pre]

Also, it was not clear to me which of the 5 columns you wanted to "suppress" for the Y value.

DISPLAY would be the wrong usage choice for your FIELD variables. You will need a usage of SUM. PROC REPORT, by default, will summarize ALL of your columns with a usage of SUM. This is OK, though, because PROC REPORT will also allow you to alter what is shown on the summary line (although not many people use this feature).

If, for example, you wanted to suppress the value for FIELD1, then you could do this:
[pre]
compute field1;
if flag = 'y' then do;
field1.sum=.;
end;
endcomp;
[/pre]

If, on the other hand, you wanted to change the value of FIELD1 to 999999, then you could do that too:
[pre]
compute field1;
if flag = 'y' then do;
field1.sum=999999;
end;
endcomp;
[/pre]

PROC REPORT is very flexible. What you have to understand is that if you want to suppress certain columns from showing on the summarized rows, you have to allow PROC REPORT to do the summary by defining your FLAG item as a GROUP usage and then defining the various FIELD items as a usage of SUM.

cynthia

topic proc report summarize in ODS and Base Reporting

proc report summarize

Re: proc report summarize