BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
9 REPLIES 9
Tim_SAS
Barite | Level 11
Are you looking for the N statistic?
deleted_user
Not applicable
Yes I guess so but I can't figure out how to apply it so that it gives me the desired result.
Tim_SAS
Barite | Level 11
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]
deleted_user
Not applicable
Thanks a lot - this was exactly what I needed 🙂
deleted_user
Not applicable
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...
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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!
Cynthia_sas
SAS Super FREQ
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]
1162
Calcite | Level 5
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]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2244 views
  • 0 likes
  • 4 in conversation