Decoding letters into numbers

[katie80]

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!

[FreelanceReinh]

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 · Accepted solution]

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]

Thank you! Simple and got the job done. 

[ballardw]

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]

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.