Help using Base SAS procedures

Decoding letters into numbers

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Decoding letters into numbers

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!

 


Accepted Solutions
Solution
‎02-01-2016 05:01 PM
Super User
Posts: 19,869

Re: Decoding letters into numbers

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


All Replies
Trusted Advisor
Posts: 1,118

Re: Decoding letters into numbers

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.

Solution
‎02-01-2016 05:01 PM
Super User
Posts: 19,869

Re: Decoding letters into numbers

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;

 

Occasional Contributor
Posts: 11

Re: Decoding letters into numbers

Thank you! Simple and got the job done. 

Super User
Posts: 11,343

Re: Decoding letters into numbers

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.

 

Contributor
Posts: 38

Re: Decoding letters into numbers

[ Edited ]
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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 334 views
  • 0 likes
  • 5 in conversation