Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- ODS Reports
- /
- Number of observations per group in proc report

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-14-2007 07:30 AM
(2488 views)

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Are you looking for the N statistic?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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]

[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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks a lot - this was exactly what I needed 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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...

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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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!

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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]

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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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]

[pre]

proc sql;

select cat "Category", count(Distinct Type) "No. of Types", sum(Qty_Sold) "Number Sold"

from fruit

group by cat;

quit;

[pre]

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

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.