Desktop productivity for business analysts and programmers

Distinct counts in Summary Table

Reply
Frequent Contributor
Posts: 104

Distinct counts in Summary Table

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.

Capture.PNG

Esteemed Advisor
Posts: 7,302

Re: Distinct counts in Summary Table

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;

Frequent Contributor
Posts: 104

Re: Distinct counts in Summary Table

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

Regular Contributor
Posts: 180

Re: Distinct counts in Summary Table

Just include the group by clause:

proc sql;

  select count(distinct guestnumber) as answer, month

    from have

    group by month

  ;

quit;

Frequent Contributor
Posts: 104

Re: Distinct counts in Summary Table

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

Esteemed Advisor
Posts: 7,302

Re: Distinct counts in Summary Table

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;

Frequent Contributor
Posts: 104

Re: Distinct counts in Summary Table

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?

Capture.PNG

Grand Advisor
Posts: 17,467

Re: Distinct counts in Summary Table

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.

Frequent Contributor
Posts: 104

Re: Distinct counts in Summary Table

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

I saved it as a dataset.

Capture.PNG

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

Capture.PNG

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

Capture.PNG

Ask a Question
Discussion stats
  • 8 replies
  • 2075 views
  • 6 likes
  • 4 in conversation