BookmarkSubscribeRSS Feed
1162
Calcite | Level 5
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]
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Possibly the COMPUTE BEFORE code block will work.

Scott Barry
SBBWorks, Inc.
Cynthia_sas
SAS Super FREQ
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
1162
Calcite | Level 5
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]
Cynthia_sas
SAS Super FREQ
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]

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
  • 4 replies
  • 1711 views
  • 0 likes
  • 3 in conversation