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]