DATA Step, Macro, Functions and more

How to count the number of unique values in Proc Sql

Accepted Solution Solved
Reply
Contributor LL5
Contributor
Posts: 44
Accepted Solution

How to count the number of unique values in Proc Sql

 

Hi, I try to count the number of unique values for product. The end result would be showing 1 in the product count column. For example. There are three "AAA" products with three different styles, I only want to show 1 in the product count column instead of 3.

I used the below code in Proc Sql, either count or count distinct did not work. Could any one advise one this? Thanks

PROC SQL;

CREATE TABLE LIST AS

SELECT

DATE,

PRODUCT,

STYLE,

COUNT(DISTINCT PRODUCT) AS PRODUCT_COUNT

FROM WAREHOUSE.INVENTORY

GROUP BY

DATE,

PRODUCT,

STYLE;

QUIT;

 

DATE

PRODUCT

STYLE

PRODUCT COUNT

10/31/2016

AAA

X

1

10/31/2016

AAA

Y

 

10/31/2016

AAA

Z

 

10/31/2016

BBB

X

1

10/31/2016

BBB

Y

 

10/31/2016

CCC

X

1

10/31/2016

CCC

Y

 

10/31/2016

DDD

X

1

10/31/2016

DDD

Y

 

10/31/2016

EEE

X

1

10/31/2016

EEE

Y

 

 

 


Accepted Solutions
Solution
‎12-02-2016 10:15 PM
Super User
Posts: 5,504

Re: How to count the number of unique values in Proc Sql

I'm not so sure that SQL is the right tool for this job  

 

If you need to, sort the data:

 

proc sort data=warehouse.inventory;

by date product style;

run;

 

Then the processing is simple for a DATA step:

 

data list;

set warehouse.inventory;

by date product style;

if first.product then product_count=1;

run;

View solution in original post


All Replies
Super User
Posts: 5,504

Re: How to count the number of unique values in Proc Sql

Is there only one DATE value throughout your entire data set?  (If influences how to count the same product if it appears on different dates.)

Contributor LL5
Contributor
Posts: 44

Re: How to count the number of unique values in Proc Sql

Posted in reply to Astounding

Yes, there's only one DATE value throughout the entire data set

Trusted Advisor
Posts: 1,019

Re: How to count the number of unique values in Proc Sql

One of your GROUP BY variables is product.  So should you expect more that one distinct product within such a group?

Contributor LL5
Contributor
Posts: 44

Re: How to count the number of unique values in Proc Sql

Since I used aggreate function to count the product in the select statment, so I just simply grouped all the variables in the group by statement.

Trusted Advisor
Posts: 1,566

Re: How to count the number of unique values in Proc Sql

You expect 1 per product, style then the count should be

   count (distinct style) as count_ 

Solution
‎12-02-2016 10:15 PM
Super User
Posts: 5,504

Re: How to count the number of unique values in Proc Sql

I'm not so sure that SQL is the right tool for this job  

 

If you need to, sort the data:

 

proc sort data=warehouse.inventory;

by date product style;

run;

 

Then the processing is simple for a DATA step:

 

data list;

set warehouse.inventory;

by date product style;

if first.product then product_count=1;

run;

Contributor LL5
Contributor
Posts: 44

Re: How to count the number of unique values in Proc Sql

Oh,nice. Great! Thanks for this approach!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 3951 views
  • 2 likes
  • 4 in conversation