How to create a new variable in SAS by extracting part of the value of an existing numeric variable?

sas

Solution

SQL(using sample data from Data Step code):

proc sql;
 create table want2 as  
 select a.subject_id, a.other, b.mom_subject_id, b.misc
 from have1 a JOIN have2 b 
  on(substr(a.subject_id,4,3)=substr(b.mom_subject_id,4,3));
quit;

Data Step:

 data have1;
  length subject_id $9;
  input subject_id $ other $;
  datalines;
   abc001def other1
   abc002def other2
   abc003def other3
   abc004def other4
   abc005def other5
  ;

 data have2;
  length mom_subject_id $9;
  input mom_subject_id $ misc $;
  datalines;
   ghi001jkl misc1
   ghi003jkl misc3
   ghi005jkl misc5
  ;

 data have1;
  length id $3;
  set have1;
  id=substr(subject_id,4,3);
 run;

 data have2;
  length id $3;
  set have2;
  id=substr(mom_subject_id,4,3);
 run;

 Proc sort data=have1;
  by id;
 run;

 Proc sort data=have2;
  by id;
 run;

 data work.want;
  merge have1(in=a) have2(in=b);
  by id;
 run;

Problem

I have two datasets in SAS that I would like to merge, but they have no common variables. One dataset has a "subject_id" variable, while the other has a "mom_subject_id" variable. Both of these variables are 9-digit codes that have just 3 digits in the middle of the code with common meaning, and that's what I need to match the two datasets on when I merge them. What I'd like to do is create a new common variable in each dataset that is just the 3 digits from within the subject ID. Those 3 digits will always be in the same location within the 9-digit subject ID, so I'm wondering if there's a way to extract those 3 digits from the variable to make a new variable. Thanks!

Original source