The SAS Output Delivery System and reporting techniques

Number of observations per group in proc report

Reply
N/A
Posts: 0

Number of observations per group in proc report

Hi,

How do I create an extra column in proc report output which displays the number of observations per group?

My code looks like this:
proc report data=xx nowd;
columns component close;
define component / group;
define close/'mean;
run;

"component" is a character variable containing product names. "close" is numeric and contains the number of days from a complaint was opened until it was solved. Proc report displays the average number of days per product but I would also like to include a column displaying the number of observations for each product e.g.

Component Number of Obs Close
ProductX 3 12
ProductZ 20 5

I have a feeling that there must be some easy way to do this but I haven't found it yet so I hope that someone can help me.

Thanks,

Helle
Super Contributor
Posts: 394

Re: Number of observations per group in proc report

Posted in reply to deleted_user
Are you looking for the N statistic?
N/A
Posts: 0

Re: Number of observations per group in proc report

Yes I guess so but I can't figure out how to apply it so that it gives me the desired result.
Super Contributor
Posts: 394

Re: Number of observations per group in proc report

Posted in reply to deleted_user
How about this?

[pre]data;
input component $8. close;
datalines;
Apples 8
Apples 12
Oranges 10
Apples 15
Bananas 4
Bananas 3
Pears 17
Oranges 14
Pears 23
Apples 12
Bananas 3
Limes 22
;;;;
proc report nowd;
columns component n close;
define component / group width=10;
define n / 'Number of Obs/Per Product' width=13;
define close / mean format=3.1 width=5;
run;[/pre]
N/A
Posts: 0

Re: Number of observations per group in proc report

Thanks a lot - this was exactly what I needed :-)
N/A
Posts: 0

Re: Number of observations per group in proc report

Posted in reply to deleted_user
I would like to add to this question:

what if those groups were more complex, like if under apple it was further broken down into stores, so if the data set looks like this...

Type Store Qty_Sold
apples store1 3
apples store2 5
apples store3 2
oranges store1 4
oranges store2 5
oranges store3 6
oranges store5 10

if you used the n statistic with type as a group, you would get 3 apples and 4 oranges. A summary break would add that to 7 types. In my situation I would only like to see 2 types and a summary break that showed only 2 types. Is there any way to accomplish this? I think it involves a compute block or a couple of blocks, but i really don't know where to start.

Sorry if i double posted...
SAS Super FREQ
Posts: 8,868

Re: Number of observations per group in proc report

Posted in reply to deleted_user
Hi:
Do you mean you do NOT want to see the 7 on the summary line, but would like to see the 2 on the summary line instead????
[pre]
Proc Report Output Summary Report

Number
type of Stores Qty_Sold
apples 3 10
oranges 4 25
N= 2 7 35

[/pre]

Does that also mean you want to see 1 instead of 3 and 1 instead of 4 or would you still want to see those numbers????

cynthia
N/A
Posts: 0

Re: Number of observations per group in proc report

Posted in reply to Cynthia_sas
Cat Type Store Qty_Sold
citrus apples store1 3
citrus apples store2 5
citrus apples store3 2
citrus oranges store1 4
citrus oranges store2 5
citrus oranges store3 6
citrus oranges store5 10

with that dataset i want to see this report

Cat # of type Total sold
citrus 2 35

thanks again!
SAS Super FREQ
Posts: 8,868

Re: Number of observations per group in proc report

Posted in reply to deleted_user
Hi:
I'd approach this one of two ways:
1) Data Step to create a "type counter" variable followed by a PROC REPORT or
2) Do the report from the Data Step

The thing is that PROC REPORT really wants the N to be the number of observations in a group. So you can sort of fake out proc report by making a numeric variable whose value is set to 1 on the first row for "apple" and then the value is set to 0 on all other apple rows, thus guaranteeing that the sum of this new variable for apple will be set to 1. Then do the same thing for oranges. The sum of this new variable will be 2 for the whole dataset -- the one above that only has apples and oranges..

cynthia
[pre]
** Make some data;
data fruit;
infile datalines;
input Cat $ Type $ Store $ Qty_Sold;
return;
datalines;
citrus apples store1 3
citrus apples store2 5
citrus apples store3 2
citrus oranges store1 4
citrus oranges store2 5
citrus oranges store3 6
citrus oranges store5 10
;
run;

proc sort data=fruit out=fruit;
by cat type;
run;

** 1) set a new type counter var (typcnt) to 1 or 0;
** in a data set and then use proc report on the NEW;
** dataset CNTFRUIT that has the TYPCNT var in it.;
data cntfruit;
set fruit end=eof;
by type;
typcnt = 0;
if first.type then typcnt = 1;
label typcnt = '# of Types';
run;

ods listing;
proc print data=cntfruit;
title 'what does the data look like';
sum typcnt qty_sold;
run;

ods html file='totsold1.html' style=sasweb;
proc report data=cntfruit nowd;
title 'Using Data Step and Proc Report';
column cat typcnt qty_sold;
define cat / group;
define typcnt / sum '# of Types';
define qty_sold / sum 'Total Sold';
run;
ods html close;

**2) Just do the report in a Data step program;
ods listing;
ods html file='totsold2.html' style=sasweb;
data _null_;
retain typcnt totsold ;
set fruit end=eof;
by type;

if first.type then typcnt + 1;
totsold + qty_sold;

if eof then do;
file print ods=(variables=(cat typcnt totsold));
put _ods_;
end;

label typcnt = '# of Types'
totsold = 'Total Sold';
run;
ods html close;

[/pre]
Frequent Contributor
Posts: 95

Re: Number of observations per group in proc report

Posted in reply to Cynthia_sas
If you know some SQL, I think this is a pretty efficient solution using the same fruit dataset:

[pre]
proc sql;
select cat "Category", count(Distinct Type) "No. of Types", sum(Qty_Sold) "Number Sold"
from fruit
group by cat;
quit;
[pre]
Ask a Question
Discussion stats
  • 9 replies
  • 265 views
  • 0 likes
  • 4 in conversation