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

How to count the number of unique values in Proc Sql

LL5 — Fri, 02 Dec 2016 22:13:38 GMT

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

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

Astounding — Fri, 02 Dec 2016 22:26:37 GMT

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.)

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

LL5 — Fri, 02 Dec 2016 22:39:14 GMT

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

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

mkeintz — Fri, 02 Dec 2016 22:42:09 GMT

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

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

LL5 — Fri, 02 Dec 2016 22:51:18 GMT

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.

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

Shmuel — Sat, 03 Dec 2016 01:30:36 GMT

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

count (distinct style) as count_

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

Astounding — Sat, 03 Dec 2016 03:14:14 GMT

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;

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

LL5 — Sat, 03 Dec 2016 03:18:33 GMT

Oh,nice. Great! Thanks for this approach!