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;
|
|
|
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;
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.)
Yes, there's only one DATE value throughout the entire data set
One of your GROUP BY variables is product. So should you expect more that one distinct product within such a group?
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.
You expect 1 per product, style then the count should be
count (distinct style) as count_
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.