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!
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;
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.
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;
Thank you! Simple and got the job done.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: