topic Re: How to join two datasets when the joining key has mixed values from two variables? in SAS Programming

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

Shradha1 — Wed, 01 Sep 2021 05:17:14 GMT

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 ?

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

japelin — Wed, 01 Sep 2021 06:18:00 GMT

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;

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

yabwon — Wed, 01 Sep 2021 09:49:29 GMT

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

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

Shradha1 — Wed, 01 Sep 2021 14:19:04 GMT

This worked. Thanks a lot!

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

Shradha1 — Wed, 01 Sep 2021 14:19:22 GMT

It's one to one join