How to join two datasets when the joining key has mixed values from two variables?

[Shradha1]

I have two datasets A and B. Dataset A has only 1 field named 'Key', the values of which can either be Account number or Card Number. It is not known which value in 'Key' is what.

Another dataset B has 3 fields: Account_num, Card_Num, and Customer_Num.

I want to left join these two datasets, on Dataset A, where I want to bring in the Customer Number from B,  corresponding to each Key in dataset A.

Is there any condition or any case-when statement that can be applied for this case, such that first A.KEY will be joined with B.Account_num, and in case there is no match found then, A.KEY will be joined with B.Card_Num to pick the customer number ?

[japelin · Accepted solution]

For example.

proc sql;
  create table c as 
    select a.*, b.* 
        from a left outer join b on (a.key=b.account_num and b.account_num ne .) or 
                                    (a.key=b.card_num and b.card_num ne .) 
;
quit;

[Shradha1]

This worked. Thanks a lot!

[yabwon]

if it's one-to-many, maybe MERGE could help:

data A;
input key;
cards;
1
2
3
4
5
10
20
30
40
50
;
run;

data b(index=(account_num card_num));
input account_num card_num name $;
cards;
1 10 A
2 .  B
. 30 C
6 60 D
;
run;

data want;
  merge A(in=inA) B(rename=(account_num=key)) B(rename=(card_num=key));
  by key;
  if inA;
run;
proc print;
run;

best

Bart

[Shradha1]

It's one to one join