topic Re: Distinct counts in Summary Table in SAS Enterprise Guide

Distinct counts in Summary Table

sdang — Fri, 27 Feb 2015 18:49:34 GMT

I have rows of data with common unique IDs. In Summary Table, how do I show a count of distinct IDs not row? Se image below for how my data looks like.

Re: Distinct counts in Summary Table

art297 — Fri, 27 Feb 2015 19:02:32 GMT

Not sure what your asking but, if the answer is 3 and your dataset is called have, then the following would work:

proc sql;

select count(distinct guestnumber)

from have

;

quit;

Re: Distinct counts in Summary Table

sdang — Fri, 27 Feb 2015 19:06:17 GMT

Yes, I'm asking for 3. Now how do I make Summary Tables show that count of distinct for each month?

Re: Distinct counts in Summary Table

CTorres — Fri, 27 Feb 2015 19:38:44 GMT

Just include the group by clause:

proc sql;

select count(distinct guestnumber) as answer, month

from have

group by month

;

quit;

Re: Distinct counts in Summary Table

sdang — Fri, 27 Feb 2015 19:51:26 GMT

Please look at fourth line. This is what I want to do. Under the heading 'Count Distinct_of_GuestNumber'n*, I want to show the count of distinct. And I know that code does not work for Proc Tabulate.

PROC TABULATE

DATA=WORK.QUERY_CLUBSERRANO

'COUNT DISTINCT_of_GuestNumber'n*

count(distinct guestnumber)

GuestNumber*

CoinIn*

Sum={LABEL=""}*F=DOLLAR16.2

ActualWin*

Sum={LABEL=""}*F=DOLLAR16.2

TheoWin*

Sum={LABEL=""}*F=DOLLAR17.2

Re: Distinct counts in Summary Table

art297 — Fri, 27 Feb 2015 20:17:46 GMT

You didn't show any date-related field in your example data. What is the field called and what does it contain?

If it is a date, and your field is called date, you might be able to use something like:

proc sql;

create table want as

select count(distinct guestnumber) as number,

month(date) as month

from have

group by month(date)

;

quit;

Re: Distinct counts in Summary Table

sdang — Fri, 27 Feb 2015 21:32:00 GMT

Hi, your advice is helpful. The report requires to look like a table using EG tool, Summary Tables. Under the column Count Distinct_of_GuestNumber, it actually has number of counts. I want distinct counts of GuestNumber. Is it possible using Summary Tables?

Re: Distinct counts in Summary Table

Reeza — Fri, 27 Feb 2015 21:38:42 GMT

Run the summary table task twice, the first time save the output to a dataset and the second time use the output from the first run as your input.

The first one generates a unique guest/day record, the second will count the number of unique guests.

Re: Distinct counts in Summary Table

sdang — Fri, 27 Feb 2015 22:03:40 GMT

I tried that but could not seem to make it work. How would I exactly do it?

I saved it as a dataset.

Then redo the Summary Tables again. It does not gave a count of distinct GuestNumber.

Can I have Distinct available in Available Statistics in Summary Tables;