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

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:

 

IDItem TypeCount
219202Srcrew Driver1
200138Srcrew Driver1
200143Srcrew Driver1
200656Srcrew Driver1
202054Wrench1
202239Srcrew Driver1
202239Srcrew Driver2
202239Wrench3
202240Srcrew Driver1
202242Srcrew Driver1
202242Wrench2
202244Srcrew Driver1
202246Srcrew Driver1
203606Srcrew Driver1
204083Wrench1
204083Wrench2
204084Wrench1
204086Wrench1
204086Wrench2
204086Wrench3

 

However, this is what I'm getting:

 

IDItem TypeCount
219202Srcrew Driver1
200138Srcrew Driver1
200143Srcrew Driver1
200656Srcrew Driver1
202054Wrench1
202239Srcrew Driver1
202239Srcrew Driver2
202239Wrench2296
202240Srcrew Driver1
202242Srcrew Driver1
202242Wrench2297
202244Srcrew Driver1
202246Srcrew Driver1
203606Srcrew Driver1
204083Wrench1
204083Wrench3
204084Wrench1
204086Wrench1
204086Wrench3
204086Wrench4

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20
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;
ballardw
Super User

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

mkeintz
PROC Star

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

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

--------------------------
cc1986
Calcite | Level 5

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!

 

 

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
  • 4 replies
  • 1158 views
  • 0 likes
  • 4 in conversation