Distinct counts in Summary Table

[sdang]

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.

[art297]

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;

[sdang]

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

[CTorres]

Just include the group by clause:

proc sql;

  select count(distinct guestnumber) as answer, month

    from have

    group by month

  ;

quit;

[sdang]

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*

   N

CoinIn*

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

ActualWin*

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

TheoWin*

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

[art297]

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;

[sdang]

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?

[Reeza]

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.

[sdang]

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;