The SAS Output Delivery System and reporting techniques

Count Distinct in Proc Report?

Reply
Frequent Contributor
Posts: 95

Count Distinct in Proc Report?

In SQL, you can use count(distinct X) to report on the number of distinct records. I'm wondering if something similar can be done with PROC REPORT. In the example below, there are two stores which I can report using PROC SQL, but PROC REPORT makes it look like there were six stores. Any idea how I can make PROC REPORT print the number of distinct stores?

The easy answer is to just use SQL, but in reality, I'm producing a much more complex table and I would really like to use the PROC REPORT procedure if at all possible.

[pre]
data tmp;
input Store :$1. Day :$2. Visitors :8.;
cards;
A 01 123
A 02 234
A 03 345
B 01 987
B 02 876
B 03 765
;
run;
[/pre]
[pre]
proc sql;
select count(distinct Store) "Number of Stores", sum(Visitors) "Visitors"
from tmp;
quit;
[/pre]
[pre]
proc report data=tmp nowindows;
columns Store,N Visitors,Sum;
run;
[/pre]
Super Contributor
Super Contributor
Posts: 3,174

Re: Count Distinct in Proc Report?

Possibly the COMPUTE BEFORE code block will work.

Scott Barry
SBBWorks, Inc.
SAS Super FREQ
Posts: 8,743

Re: Count Distinct in Proc Report?

Hi:
There -will- be a difference with the output when you compare sql output vs report output. For example, the SQL output would look like this:
[pre]
SQL Output is 1 line

Number
of
Stores Visitors
------------------
2 3330
[/pre]
while the PROC REPORT output would look like this:
[pre]
REPORT Output is 1 line per store plus total

Number Number
of of
Store Stores Days Visitors
A 1 3 702
B 1 3 2628
2 6 3330

[/pre]

Even if you did a NOPRINT on Store and only showed the computed item for Number of stores, number of days and visitors, the report would still have 3 lines.

So, if the requirement is to EXACTLY duplicate the SQL, then the PROC REPORT approach means making 2 runs of the procedure. The first run could create an output dataset with the summary information (using OUT=) and the second run could just display the "grand total" line (using the _BREAK_ variable that REPORT creates).

This may be one of those cases where the simpler solution (SQL) is the one to use -- unless you absolutely need to switch from SQL to REPORT for some reason. There's also the DATA step, using FIRST.STORE to set a counter.

cynthia
Frequent Contributor
Posts: 95

Re: Count Distinct in Proc Report?

Cynthia,

Thanks for your reply. A combination of your suggestions gave me exactly what I wanted. If I sort my dataset and use FIRST.STORE, I can set a flag for each new store. Then I just count the flags instead of the stores in the report. Here's my solution:

[pre]
data tmp;
input Store :$1. Day :$2. Visitors :8.;
cards;
A 01 123
A 02 234
A 03 345
B 01 987
B 02 876
B 03 765
;
run;

proc sort data=tmp; by store; run;

data tmp;
set tmp;
by store;
if first.store then Distinct = 1;
run;

proc report data=tmp nowindows;
columns Distinct,N Visitors,Sum;
run;
[/pre]
SAS Super FREQ
Posts: 8,743

Re: Count Distinct in Proc Report?

Hi:
That's one way. Or, if you are going to create your counter for unique stores up in the data step, you could just do the entire report in the DATA step -- by just testing whether you're at the end of file -- you can have the DATA step accumulate your totals and then only write them out one time. The END= option lets you name a variable that you can test to detect when the end of the input file has been reached. The value of your variable (EOF) is 0 for all the rows and then EOF=1 on the last row.

cynthia
[pre]
ods listing;
ods html file='c:\temp\data_null.html' style=sasweb;
title 'With Data Step';

data _null_;
set tmp end=eof;
length Distinct TotVisit 8;
by store;
retain distinct totvisit 0;
if first.store then do;
Distinct + 1;
end;
totvisit + visitors;
if eof = 1 then do;
file print ods=(variables=(distinct totvisit));
put _ods_;
end;
label Distinct = 'Number of Stores'
Totvisit = 'Total Visitors';
format Distinct Totvisit comma6.;
run;

ods html close;
[/pre]
Ask a Question
Discussion stats
  • 4 replies
  • 260 views
  • 0 likes
  • 3 in conversation