turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Distinct counts in Summary Table

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2015 01:49 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sdang

02-27-2015 02:02 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-27-2015 02:06 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sdang

02-27-2015 02:38 PM

Just include the **group by** clause:

proc sql;

select count(distinct guestnumber) as answer, month

from have

group by month

;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to CTorres

02-27-2015 02:51 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sdang

02-27-2015 03:17 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-27-2015 04:32 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sdang

02-27-2015 04:38 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-27-2015 05:03 PM

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;