hi,
When joining two tables, I need to see all records from table A, that match only once to records from table B. In othe words, if table B has 3 obvervations that matches 1 obs from table A, I only want to see 1 'joined' observations (or if you know of another way to arrive at this result).
thank you in advance. Sample code below
Data table1; Input ID $ Account_ID $ Amount; Datalines; 001 2001 13 002 2002 5 003 2002 4 004 2003 1 Run; Data table2; Input ID $ Account_ID $ Amount; Datalines; 001 2001 13 002 2001 13 003 2001 13 004 2001 13 005 2003 1 Run; Proc sql; create table GET as Select a.*, b.ID AS ID2, B.ACCOUNT_ID AS ACCOUNT_ID2,B. AMOUNT AS AMOUNT2 FROM TABLE1 AS A LEFT JOIN TABLE2 AS B ON A.ACCOUNT_ID=B.ACCOUNT_ID AND A.AMOUNT=B.AMOUNT;QUIT; Data WANT; Input ID $ Account_ID $ Amount ID2 $ ACCOUNT_ID2 $ AMOUNT2 ; Datalines; 001 2001 13 001 2001 13 002 2002 5 "" "" null 003 2002 4 "" "" null 004 2003 1 005 2003 1 Run;
(note that the "" in syntax of table Want is meant to appear as blank value)
In Sql you can do this way
Proc sql; select a.*, b.* from (Select * FROM TABLE1)a left join (select min(ID) as ID, account_id, amount from table2 group by account_id, amount )b on A.ACCOUNT_ID=B.ACCOUNT_ID AND A.AMOUNT=B.AMOUNT; QUIT;
Use a data step:
proc sort data=table1;
by account_id amount;
run;
proc sort data=table2;
by account_id amount;
run;
data want;
merge
table1 (in=a)
table2 (in=b rename=(id=id2))
;
by account_id amount;
if a;
if first.amount;
run;
proc sort data=want;
by id;
run;
proc print data=want noobs;
run;
Result:
Account_ ID ID Amount id2 001 2001 13 001 002 2002 5 003 2002 4 004 2003 1 005
I omitted account_id2 and amount2, as they are redundant.
In Sql you can do this way
Proc sql; select a.*, b.* from (Select * FROM TABLE1)a left join (select min(ID) as ID, account_id, amount from table2 group by account_id, amount )b on A.ACCOUNT_ID=B.ACCOUNT_ID AND A.AMOUNT=B.AMOUNT; QUIT;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.