topic Merging tables on shortened version of variable in SAS Programming

Merging tables on shortened version of variable

jmmedina25 — Mon, 05 Aug 2019 16:37:49 GMT

Hi! I need to merge two tables, but the 2 variables that I'm matching on don't match exactly. The variable in the first table is 11 characters and the other is 9 (between the 2nd and 9th digit of the first). I would like to do this in a proc sql step if possible.

table 1

z2345678900

x3333333300

table 2

23456789

33333333

Re: Merging tables on shortened version of variable

Jagadishkatam — Mon, 05 Aug 2019 16:53:06 GMT

Please try the below code

data table1;
input text$20.;
cards;
z2345678900
x3333333300
;
 
data table2;
input text$;
cards;
23456789
33333333
;


proc sql;
create table want as select a.text, b.text as text2 from table1 as a , table2 as b where substr(a.text,2,8)=b.text;
quit;