SAS: merge/ join datasets by dynamic columns in the look up table

Question

I want to join sas dataset with the look up table but the column/key for joining is a value in the look up table

Dataset: table4

             ID  lev1   lev2   lev3   lev4  lev5
              1  12548  14589  85652  45896 45889
              2  12548  14589  85652  45896 45890
              3  12548  14547  85685  45845 45825
              4  66588  24647  55255  30895 15764

Look up table:

           context   table_name column operator value
            extract   table1     col1  equals  xyd
            asset     table2     var1  equals  11111
            asset     table2     var2  equals  25858
            prod      table3     x1    equals  87999
            unprod    table4     lev2  equals  14589
            unprod    table4     lev2  equals  14589
            unprod    table4     lev3  equals  55255

Now I want to join table4 with lookup table but it is only possible with fields lev2 and lev3(it is dynamic so could be changed in the future, so don't want to hardcode in it).

I have tried below code but doesn't want to hard code as the fields are dynamic( someone might add lev4 as well in future).

proc sql ;
create table want as 
select ID
from  table4 as a 
inner join  lookup as b 
on  a.lev2 = input(value,12.) or a.lev3=input(value,12.)
where Context="unprod";
quit;

Thanks heaps in advance.

Can you transpose the first table so that you only have a single column for the join? This would avoid the problem entirely? — Reeza, Apr 13 '18 at 02:31
Thanks for your response. Unfortunately there are 1000's of values in each level and also there are about 20 levels. Above tables are just example to explain what I'm trying to achieve. — Bharath, Apr 13 '18 at 02:37
That makes the case for transposing the data stronger doesn't it. This way, it doesn't matter how many columns there would be or how many levels either, the solution would be entirely dynamic.... — Reeza, Apr 13 '18 at 02:41
Post what you expect as output, and your data as a data step and I'll mock up the solution. If you don't know how to include as a data step instructions are here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 — Reeza, Apr 13 '18 at 02:43
That isn't a lookup table. It is a set of rules. What do you want to do with the rules? — Tom, Apr 13 '18 at 05:27

score 3 · Answer 1 · answered Apr 13 '18 at 03:29

Here is a sample code that would get what I understood you are trying to do. This code is based on the comment by @Reeza. If this is not what you are trying to do, please send a sample output file.

data table4;
input              ID $ lev1 $  lev2 $  lev3 $  lev4 $ lev5 $;
datalines;
              1  12548  14589  85652  45896 45889
              2  12548  14589  85652  45896 45890
              3  12548  14547  85685  45845 45825
              4  66588  24647  55255  30895 15764
;
run;

data look_up;
input context $  table_name $ column $ operator $ value $;
datalines;
            extract   table1     col1  equals  xyd
            asset     table2     var1  equals  11111
            asset     table2     var2  equals  25858
            prod      table3     x1    equals  87999
            unprod    table4     lev2  equals  14589
            unprod    table4     lev2  equals  14589
            unprod    table4     lev3  equals  55255
;
run;



PROC transpose DATA=work.table4 out=temp1  prefix=value;
    by ID;
    VAR lev1-lev5;
run;

proc sql;
    create table want as 
    select a.*, b.ID 
    from look_up as a 
    inner join temp1 as b 
    on a.value=b.value1 and a.column=_Name_;
quit;

score 3 · Accepted Answer · answered Apr 13 '18 at 05:44

That does not look like a lookup table. It appears to be a set of rules. You could use it to generate code. Let's simplify the process by making the table contain actual code instead of three columns. But you could easily write the code to convert from your current format into code strings.

data rules ;
  infile cards truncover ;
  input context $ table_name $  rule $100. ;
cards;
extract   table1     col1  =  xyd
asset     table2     var1  =  11111
asset     table2     var2  =  25858
prod      table3     x1    =  87999
unprod    table4     lev2  =  14589
unprod    table4     lev2  =  14589
unprod    table4     lev3  =  55255
;

So now it looks like you want to take the rules that have a specific value of CONTEXT and use that to generate a new dataset from the dataset named in TABLE_NAME. Not sure what name you want to use for the generated table or what you want to do when more than one table is mentioned in the same "context".

%let context=unprod ;

filename code temp;
data _null_;
  set rules ;
  where context=symget('context');
  by table_name ;
  file code ;
  if first.table_name then table_no+1;
  if first.table_name then put
  'data want' table_no ';'
/ '  set ' table_name ';'
/ '  where 1=0'
  ;
  put '  or (' rule ')' ;
  if last.table_name then put
  ';'
/ 'run;'
  ;
run;

%include code / source2 ;

Which results in code like this:

130  +data want1 ;
131  +  set table4 ;
132  +  where 1=0
133  +  or (lev2  =  14589 )
134  +  or (lev2  =  14589 )
135  +  or (lev3  =  55255 )
136  +;
137  +run;

NOTE: There were 3 observations read from the data set WORK.TABLE4.
      WHERE (lev2=14589) or (lev3=55255);
NOTE: The data set WORK.WANT1 has 3 observations and 6 variables.

SAS: merge/ join datasets by dynamic columns in the look up table

2 Answers2