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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.