topic Re: Join two datasets with different structure (similar to vlookup) in SAS Programming

Join two datasets with different structure (similar to vlookup)

nemez — Thu, 18 Jul 2024 18:50:04 GMT

Hi everyone,

I have two tables A and B and need to join them based on two variables from A which correspond to differently structured variables from B.

data a;
  input id $ var1 $ var2 $;
datalines;
1 1 1
1 3 2
2 2 4
2 4 3
run;

data b;
  input var2 $ var1_1 $ var1_2 $ var1_3 $ var1_4 $;
datalines;
1 a . b2 a .
2 . b1 b3 .
3 . . b4 b2
4 . . . a
run;

data want;
  input id $ var1 $ var2 $ type $;
datalines;
1 1 1 a
1 3 2 .
2 2 4 .
2 4 3 b2
;

Thereby, the dots represent empty cells.

This left join would also be possible with Excel vlookup and two criteria, however Excel's performance is too weak for the actual datasets and I'm sure there is an elegant way to do this also with SAS in one step. Any suggestions of how to do this are appreciated.

Re: Join two datasets with different structure (similar to vlookup)

Tom — Fri, 19 Jul 2024 13:42:42 GMT

Normalize the "lookup" table. Then it is much easier to deal with. No need to keep the empty cells. Example using the same character variables.

data lookup
  length row col $8 ;
  input row @ ;
  do col='1','2','3','4';
    input type $ @;
    if not missing(type) then output;
  end;
datalines;
1 a . b2 a .
2 . b1 b3 .
3 . . b4 b2
4 . . . a
;

Now you can use SQL or data step MERGE to combine the two.

Or even load it into a hash object and use the FIND() hash object method to retrieve the TYPE.

But it probably makes more sense if you are trying to use VLOOKUP type capabilities if the index variables are numeric.

data lookup
  row+1;
  do col=1 to 4;
    input type $ @;
    if not missing(type) then output;
  end;
datalines;
a . b2 a .
. b1 b3 .
. . b4 b2
. . . a
;

Re: Join two datasets with different structure (similar to vlookup)

Ksharp — Fri, 19 Jul 2024 01:10:21 GMT

data a(index=(var2));
  input id $ var1 $ var2 $;
datalines;
1 1 1
1 3 2
2 2 4
2 4 3
;
data b(index=(var2));
  input var2 $ var1_1 $ var1_2 $ var1_3 $ var1_4 $;
datalines;
1 a . b2 a .
2 . b1 b3 .
3 . . b4 b2
4 . . . a
;

data want;
 merge a b;
 by var2;
 want=vvaluex(cats('var1_',var1));
 drop var1_:;
run;

Re: Join two datasets with different structure (similar to vlookup)

s_lassen — Fri, 19 Jul 2024 10:44:30 GMT

Assuming the table B is already sorted by VAR2, I would use a solution like this:

proc sort data=a;
  by var2;
run;

data want;
 merge a b;
 by var2;
 array var1_ (*) var1_1-var1_4;
 type=var1_(var1);
 drop var1_:;
run;

You may then want to sort back by ID and VAR1, to get data in the same order as your example.

But I do not understand the second observation in your WANT example: should the TYPE variable not be "b3" here (VAR1_3 from the row in B with VAR2=2)?

Re: Join two datasets with different structure (similar to vlookup)

nemez — Fri, 19 Jul 2024 16:38:07 GMT

Hello Ksharp,

thank you very much, your solution works fine.

Greetings

Re: Join two datasets with different structure (similar to vlookup)

nemez — Fri, 19 Jul 2024 16:42:48 GMT

Thank you for your suggestion and you are right about my mistake in the want example. However, I have not yet tested your approach.

Re: Join two datasets with different structure (similar to vlookup)

nemez — Fri, 19 Jul 2024 16:44:45 GMT

Thank you very much for all the contributions!