topic Re: Left Join in SAS Programming

Left Join

sanjay1 — Wed, 09 Aug 2017 11:04:34 GMT

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

Re: Left Join

PaigeMiller — Wed, 09 Aug 2017 11:06:57 GMT

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.

Re: Left Join

sanjay1 — Wed, 09 Aug 2017 11:06:35 GMT

Hi PaigeMiller,

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

Re: Left Join

Kurt_Bremser — Wed, 09 Aug 2017 11:06:19 GMT

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?

Re: Left Join

sanjay1 — Wed, 09 Aug 2017 11:11:14 GMT

It is less than area in dataset a

Re: Left Join

Kurt_Bremser — Wed, 09 Aug 2017 11:17:56 GMT

@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                    .

Re: Left Join

Ksharp — Wed, 09 Aug 2017 12:49:43 GMT

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;