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

I have a variable that is a random combination of letters and numbers and I want to convert it into numbers to give each combination an id number. What I have looks like this: 

 

raw

0004BG

0004BG

0004BG

0004BG

G45700

G45700

L43709

L43709

L43709

...

 

What I want is:

id

1

1

1

1

2

2

3

3

3

...

 

 

What is the easiest way to do this?

 

Thank you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

You can use BY group processing to identify your groups. Assuming they're sorted already. BY group processing is very powerful and it's worth reading up a bit on how it works. 

Here's a specific example for you:

http://www.ats.ucla.edu/stat/sas/faq/enumerate.htm

And the SAS documentation:

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000761932.htm

 

data want;
set have;
by ID;

retain count;

if first.ID then count+1;

run;

 

View solution in original post

5 REPLIES 5
FreelanceReinh
Jade | Level 19
data have;
input raw $;
cards;
0004BG
0004BG
0004BG
0004BG
G45700
G45700
L43709
L43709
L43709
;

data want;
set have;
by raw;
id+first.raw;
run;

If HAVE is not sorted by RAW, but equal RAW values are grouped, please modify the BY statement to:

by raw notsorted;

If HAVE is neither sorted nor grouped, please sort HAVE by RAW before creating WANT.

Reeza
Super User

You can use BY group processing to identify your groups. Assuming they're sorted already. BY group processing is very powerful and it's worth reading up a bit on how it works. 

Here's a specific example for you:

http://www.ats.ucla.edu/stat/sas/faq/enumerate.htm

And the SAS documentation:

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000761932.htm

 

data want;
set have;
by ID;

retain count;

if first.ID then count+1;

run;

 

katie80
Fluorite | Level 6

Thank you! Simple and got the job done. 

ballardw
Super User

If the data is grouped so that all of your records for an ID are together something like this might work:

data want;
   set have (rename=(id=oldId));
   by notsorted oldId;
   retain id 0;
   if first.oldid then id+1;
   /* if you don't want the oldid then add:
   drop oldid;
   I wouldn't until verifying everything worked as intended.
   */
run;

Or sort by the ID before this data step.

 

DanZ
Obsidian | Level 7
proc sql;
create table id as select
raw,
monotonic() as id
from (select distinct raw from have)
;
run;

create table want as select 
t1.raw,
t2.id
from have t1
left join id t2
on t1.raw = t2.raw
;
run;
quit;

A potential SQL solution as well. Not my favorite, but you wouldn't have to sort anything separately.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 987 views
  • 0 likes
  • 5 in conversation