BookmarkSubscribeRSS Feed
sanjay1
Obsidian | Level 7


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

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
sanjay1
Obsidian | Level 7

Hi PaigeMiller,

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

sanjay1
Obsidian | Level 7

It is less than area in dataset a

Kurt_Bremser
Super User

@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                    .           
Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 559 views
  • 0 likes
  • 4 in conversation