0

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

left_joins_are_for_cowards
  • 33
  • 5
  • Just a hunch, but have you tried - `on upcase(compress(substr(a.x_1,1,2))) = upcase(compress(b.ID))` or `strip` instead of `compress`? Also, check if data types are same in `a` and `b`. `substr` might be returning nulls in your case. – samkart Nov 09 '18 at 16:13
  • 2
    Is the `_transformed` table already transposed ? If not show the transpose code. – Richard Nov 09 '18 at 16:29
  • Please follow the guidelines for providing a [minimal, complete and verifiable example](https://stackoverflow.com/help/mcve) as part of your question. At the moment we can't reproduce your issue from the details you've provided. – user667489 Nov 12 '18 at 13:58

0 Answers0