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
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.
Hi PaigeMiller,
Area(Abc and bca) and Common_var In table "A" should match with area (ab,bc) and common_var in B.
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?
It is less than area in dataset a
@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 .
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;
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.