How can I use a data step to create a count unique by ID and category. I want the same count to remain for non unique values. Please see example attached and the wanted counts. Thanks
data have;
input (ID CATEGORY) ($);* WANTED COUNT;
cards;
001 PIG 1
001 PIG 1
003 SHEEP 1
003 PIG 2
003 PIG 2
004 SHEEP 1
004 SHEEP 1
005 PIG 1
005 PIG 1
005 COW 2
005 SHEEP 3
;
data want;
set have;
by id category notsorted;
if first.id then count=1;
else if first.category then count+1;
run;
data have;
input (ID CATEGORY) ($);* WANTED COUNT;
cards;
001 PIG 1
001 PIG 1
003 SHEEP 1
003 PIG 2
003 PIG 2
004 SHEEP 1
004 SHEEP 1
005 PIG 1
005 PIG 1
005 COW 2
005 SHEEP 3
;
data want;
set have;
by id category notsorted;
if first.id then count=1;
else if first.category then count+1;
run;
Presuming the data is grouped into contiguous blocks the syntax BY var1 var2 NOTSORTED;
can be used. This assumption is made because group 5 is not sorted in alphabetical order.
data have; input
ID CATEGORY $ wanted; datalines;
001 PIG 1
001 PIG 1
003 SHEEP 1
003 PIG 2
003 PIG 2
004 SHEEP 1
004 SHEEP 1
005 PIG 1
005 PIG 1
005 COW 2
005 SHEEP 3
;
data want;
set have;
by id category notsorted;
if first.id then seq=0;
if first.category then seq+1;
run;
Output
Thank you so much - that's perfect!
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.