left join on first two words in a string using comma delimiter SAS

307 Views Asked by At

I have a dataset and a dictionary like such

data have_data;
input y x_1;
datalines;
1 10
2 20
3 30
;
run;

data have_dictionary;
input ID label;
datalines;
X_1 Assets
;
run;

I then transform my above dataset to create log and squared transforms of the variables like such

data have_transformed;
input y x1 x1_log x1_sq;
datalines;
1 10 1 100
2 20 1.30 400
3 30 1.48 900
;
run;

I then need to append my dictionary for x_1 , x_1_log, x_1_sq, i've tried using the scan, trim and substr function but I cant find a way to match on 2 out of 3 words like so

PROC SQL;
CREATE TABLE TESTING_MERGE AS
SELECT
    a.*,
    b.Label
FROM have_T as a
left join have_dictionary as b
on substr(a.x_1,1,2), = b.ID;

quit;

Note, I have transposed my have table to make it better for joining

0

There are 0 best solutions below