topic How to create NewIDs based on Macro IDs list? in SAS Programming

How to create NewIDs based on Macro IDs list?

ybz12003 — Sun, 16 Feb 2020 21:32:19 GMT

Hello,

I have a data X which contains macro ID list, and I have a Sample dataset Y which contains all different kind of formats. I would like to create a NewID (see data Want) based on the macro ID list from TempID_1 to TempID_4 (see data Y). Is there a way to approach this? Thank you.

data x;
format ID $10. ;
input ID $10. ;
cards;
1156
1789
0001147
0001597
0598
0002489
;
run;
 
data y;
      infile datalines dsd;
  input TempID_1 : $15. TempID_2 : $15.  TempID_3 : $15. TempID_4 : $15.;
datalines;
21156, , , ,
, 000-1147, , ,    
, , Car_1597, ,
, , , ID 1789,
C1156, , , ,
, 1789 Car,  , ,
, , ID__0598-Car, ,
200, 9, X1O23, UP689,
2001156, , , ,
, , ,  1597 UP,
208, 9,  8MO23, UV689,
, 8,  HL 2489, GJ627,
;



data want;
	infile datalines dsd;
  input TempID_1 : $15. TempID_2 : $15.  TempID_3 : $15. TempID_4 : $15. NewID : $15. ;
datalines;
21156, , , , 1156,
, 000-1147, , , 000147,   
, , Car_1597, , 0001597,
, , , ID 1789, 1789,
C1156, , , , 1156,
, 1789 Car,  , , 1789,
, , ID__0598-Car, , 0598,
200, 9, X1O23, UP689, ,
2001156, , , , 1156,
, , ,  1597 UP, 0001579,
208, 9,  8MO23, UV689, ,
, 8,  HL 2489, GJ627, 0002489,
;

Re: How to create NewIDs based on Macro IDs list?

ChrisNZ — Mon, 17 Feb 2020 09:18:34 GMT

Like this?

select *
from HAVE2 left join HAVE1
  on cats(of TempID_1-TempID_4) contains put(input(ID,32.),z4.) ;

[Edit: corrected code]

Re: How to create NewIDs based on Macro IDs list?

Ksharp — Mon, 17 Feb 2020 06:09:56 GMT

data x;
format ID $10. ;
input ID $10. ;
cards;
1156
1789
0001147
0001597
0598
0002489
;
run;
 
data y;
      infile datalines dsd;
  input TempID_1 : $15. TempID_2 : $15.  TempID_3 : $15. TempID_4 : $15.;
datalines;
21156, , , ,
, 000-1147, , ,    
, , Car_1597, ,
, , , ID 1789,
C1156, , , ,
, 1789 Car,  , ,
, , ID__0598-Car, ,
200, 9, X1O23, UP689,
2001156, , , ,
, , ,  1597 UP,
208, 9,  8MO23, UV689,
, 8,  HL 2489, GJ627,
;

proc sql;
select *
from Y left join X
  on catx('|',TempID_1,TempID_2,TempID_3,TempID_4) contains strip(prxchange('s/^0+//',1,ID)) ;
 quit;