The core node you are trying to access is permanently deleted.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.