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 ?
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;
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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.