topic sql outer join, limit records from table b in SAS Programming

sql outer join, limit records from table b

brulard — Tue, 11 Jul 2017 12:34:49 GMT

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)

Re: sql outer join, limit records from table b

Kurt_Bremser — Tue, 11 Jul 2017 12:53:15 GMT

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.

Re: sql outer join, limit records from table b

kiranv_ — Tue, 11 Jul 2017 13:22:44 GMT

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;