Left Join

Reply
Frequent Contributor
Posts: 114

Left Join

[ Edited ]


Hi SAS Experts,


I have two tables a and b, both of them have common variables "Common_var" and "area", I want to bring all the records from table
"a" and matchings from table "b", it should match on both common variables "common_var" and "area", but the problem is 'area' in b table is not exactly matching with area in a table. Is there is anyway to get the below result.

 

data a;
input area$ marks common_var$;
datalines;
abc 20 a
abc 30 a
bca 10 a
bca 20 a
vcv 19 a
fgf 30 a
ffg 49 a
;
run;

 

data b;
input area$ year subj$ common_var$;
datalines;
ab 2015 maths a
bc 2016 science a
;
run;

 


result
area marks common_var year subj
abc 20 a 2015 maths
abc 30 a 2015 maths
bca 10 a 2016 science
bca 20 a 2016 science
vcv 19 a
fgf 30 a
ffg 49 a

 

Thanks & regards,

Sanjay

Trusted Advisor
Posts: 1,607

Re: Left Join

[ Edited ]

Can you state the rules by which matching should happen (and also which things won't match)? If so, then the code could be written. But we can't write code without that information.

Frequent Contributor
Posts: 114

Re: Left Join

[ Edited ]

Hi PaigeMiller,

Area(Abc and bca) and Common_var In table "A" should match with area (ab,bc) and common_var in B.

Super User
Posts: 6,928

Re: Left Join

[ Edited ]

Is area in dataset b of a fixed length (less than area in dataset a), or could it be of equal length to area in dataset a?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 114

Re: Left Join

It is less than area in dataset a

Super User
Posts: 6,928

Re: Left Join


sanjay1 wrote:

It is less than area in dataset a


Then take account of that in the condition for the join:

proc sql;
create table want as
select
  a.*,
  b.area as area_b,
  b.year,
  b.subj
from
  a left join
  b
  on substr(a.area,1,length(b.area)) = b.area
  and a.common_var = b.common_var
;
quit;

proc print data=want noobs;
run;

Result:

                 common_
area    marks      var      area_b    year     subj

abc       20        a         ab      2015    maths  
abc       30        a         ab      2015    maths  
bca       10        a         bc      2016    science
bca       20        a         bc      2016    science
fgf       30        a                    .           
vcv       19        a                    .           
ffg       49        a                    .           
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,670

Re: Left Join

data a;
input area$ marks common_var$;
datalines;
abc 20 a
abc 30 a
bca 10 a
bca 20 a
vcv 19 a
fgf 30 a
ffg 49 a
;
run;
 
data b;
input area$ year subj$ common_var$;
datalines;
ab 2015 maths a
bc 2016 science a
;
run;

proc sql;
select a.*,year,subj
 from a left join b 
  on a.common_var=b.common_var and
     b.area eqt a.area;
quit;
Ask a Question
Discussion stats
  • 6 replies
  • 113 views
  • 0 likes
  • 4 in conversation