turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- Number of observations per group in proc report

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-14-2007 07:30 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

09-14-2007 07:56 AM

Are you looking for the N statistic?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Tim_SAS

09-18-2007 03:30 AM

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

09-18-2007 09:21 AM

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Tim_SAS

09-20-2007 01:34 AM

Thanks a lot - this was exactly what I needed :-)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

03-14-2008 12:32 AM

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

03-14-2008 02:00 PM

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

03-14-2008 02:24 PM

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

03-14-2008 11:25 PM

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

03-24-2008 01:47 PM

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]