topic Re: Decoding letters into numbers in SAS Procedures

Decoding letters into numbers

katie80 — Mon, 01 Feb 2016 21:21:29 GMT

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

G45700

L43709

...

What I want is:

...

What is the easiest way to do this?

Thank you!

Re: Decoding letters into numbers

FreelanceReinh — Mon, 01 Feb 2016 21:31:06 GMT

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.

Re: Decoding letters into numbers

Reeza — Mon, 01 Feb 2016 21:32:16 GMT

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;

Re: Decoding letters into numbers

ballardw — Mon, 01 Feb 2016 21:35:31 GMT

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.

Re: Decoding letters into numbers

DanZ — Mon, 01 Feb 2016 21:43:31 GMT

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.

Re: Decoding letters into numbers

katie80 — Mon, 01 Feb 2016 22:02:18 GMT

Thank you! Simple and got the job done.