Hi,
I'm trying to get my enumeration variable to work but I'm getting funky results. I need to set up a counter that will start at one for a specific item type and then add one for each additional ID. For example this is what I would want:
ID | Item Type | Count |
219202 | Srcrew Driver | 1 |
200138 | Srcrew Driver | 1 |
200143 | Srcrew Driver | 1 |
200656 | Srcrew Driver | 1 |
202054 | Wrench | 1 |
202239 | Srcrew Driver | 1 |
202239 | Srcrew Driver | 2 |
202239 | Wrench | 3 |
202240 | Srcrew Driver | 1 |
202242 | Srcrew Driver | 1 |
202242 | Wrench | 2 |
202244 | Srcrew Driver | 1 |
202246 | Srcrew Driver | 1 |
203606 | Srcrew Driver | 1 |
204083 | Wrench | 1 |
204083 | Wrench | 2 |
204084 | Wrench | 1 |
204086 | Wrench | 1 |
204086 | Wrench | 2 |
204086 | Wrench | 3 |
However, this is what I'm getting:
ID | Item Type | Count |
219202 | Srcrew Driver | 1 |
200138 | Srcrew Driver | 1 |
200143 | Srcrew Driver | 1 |
200656 | Srcrew Driver | 1 |
202054 | Wrench | 1 |
202239 | Srcrew Driver | 1 |
202239 | Srcrew Driver | 2 |
202239 | Wrench | 2296 |
202240 | Srcrew Driver | 1 |
202242 | Srcrew Driver | 1 |
202242 | Wrench | 2297 |
202244 | Srcrew Driver | 1 |
202246 | Srcrew Driver | 1 |
203606 | Srcrew Driver | 1 |
204083 | Wrench | 1 |
204083 | Wrench | 3 |
204084 | Wrench | 1 |
204086 | Wrench | 1 |
204086 | Wrench | 3 |
204086 | Wrench | 4 |
Here's my code:
proc sort data=need_id;
by id type;
run;
data need_id2;
set need_id;
by id;
count+1;
if first.id then count=1;
run;
My thought is that if I sort the data set in the way that I want, with specific items being counted first, that the proper count will be assigned. However, that's not the case and I don't see a pattern as to why the counter isn't working.
Any thoughts or suggestions would be extreemly helpful!
Thanks in advance!
Since you have data like this
202242 | Srcrew Driver | 1 |
202242 | Wrench | 2297 |
where the ID is duplicated did mean to count Id Type combinations, Id's or Types?
It sounds like you may have meant:
data need_id2; set need_id; by id type; count+1; if first.type then count=1; run;
given your sort order.
You may also have some issues with your input data but since you did not provide that I'm not sure.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
And someone needs to learn how to specll screw driver
data have;
input ID$ Item_Type$20.;
infile datalines dlm=',' dsd;
datalines;
219202,Srcrew Driver
200138,Srcrew Driver
200143,Srcrew Driver
200656,Srcrew Driver
202054,Wrench
202239,Srcrew Driver
202239,Srcrew Driver
202239,Wrench
202240,Srcrew Driver
202242,Srcrew Driver
202242,Wrench
202244,Srcrew Driver
202246,Srcrew Driver
203606,Srcrew Driver
204083,Wrench
204083,Wrench
204084,Wrench
204086,Wrench
204086,Wrench
204086,Wrench
;
proc sort data=have;
by id;
run;
data want;
set have;
by id;
counter + 1;
if first.id then counter=1;
run;
Since you have data like this
202242 | Srcrew Driver | 1 |
202242 | Wrench | 2297 |
where the ID is duplicated did mean to count Id Type combinations, Id's or Types?
It sounds like you may have meant:
data need_id2; set need_id; by id type; count+1; if first.type then count=1; run;
given your sort order.
You may also have some issues with your input data but since you did not provide that I'm not sure.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
And someone needs to learn how to specll screw driver
I see nothing wrong with your code.
However ... is variable COUNT already in your data set NEED_ID? That could produce the weird results.
If so, then change
SET NEED_ID ;
to
SET NEED_ID (drop=count);
PRFOC SORT
Hi,
Man, I feel like a goof! Thanks to your suggestions, I realized that there was already a count variable in my dataset, which was causing the error. I dropped the historic count variable and then my code worked! No more coding late on a Friday afternoon after a long week!
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.