BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

 

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
Astounding
PROC Star

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

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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.

Shmuel
Garnet | Level 18

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

   count (distinct style) as count_ 

Astounding
PROC Star

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;

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9
Oh,nice. Great! Thanks for this approach!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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