Desktop productivity for business analysts and programmers

How to join/merge tables (vlookup) not based on exact match?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

How to join/merge tables (vlookup) not based on exact match?

data source;
  infile datalines dlm=",";
  input date date9.;
datalines;
01JAN2015
01JAN2016
01SEP2016
;
run;

data source;
set source;
format date date9.;
run;

data ref;
  infile datalines dlm=",";
  input date2 date9.;
datalines;
01JAN2011
01JUN2016
;
run;

data ref;
set ref;
format date2 date9.;
run;

After joining them, the final dataset would look like:

date                date2

01JAN2015   01JAN2011             /* because 01/01/2015 >= 01/01/2011 and < 01/06/2016 */

01JAN2016   01JAN2011

01SEP2016   01JUN2016

 

Typically in Excel, one would use "=VLOOKUP($C4,$H$4:$I$5,1,TRUE)" (for the 2nd column). I believe there is a similar concept in SAS.

 

How to achieve it?


Accepted Solutions
Solution
‎07-17-2017 03:18 AM
Frequent Contributor
Posts: 82

Re: How to join/merge tables (vlookup) not based on exact match?

proc sql noprint;
create table temp as 
  select * from work.source, work.ref           /* cartesian product */
  where date > date2                                   
  order by date, date2;
quit;

data want;
set temp;
by date;
if last.date;
run; 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

Re: How to join/merge tables (vlookup) not based on exact match?

First tranpose the ref dataset and then merge with source via proc sql as below to get the expected output

 

 

proc transpose data=ref out=trans;
var date2;
run;

proc sql;
create table want as select a.date, case when a.date>=b.col1 and a.date <=b.col2 then b.col1 else b.col2 end as date2 format=date9. from source as a , trans as b;
quit;
Thanks,
Jag
Frequent Contributor
Posts: 82

Re: How to join/merge tables (vlookup) not based on exact match?

Hi @Jagadishkatam, thank you for your quick response!

This approach definitely works on the simplified example above. However, in my ref dataset, the number of rows is dynamic (sometimes more than 20).

Solution
‎07-17-2017 03:18 AM
Frequent Contributor
Posts: 82

Re: How to join/merge tables (vlookup) not based on exact match?

proc sql noprint;
create table temp as 
  select * from work.source, work.ref           /* cartesian product */
  where date > date2                                   
  order by date, date2;
quit;

data want;
set temp;
by date;
if last.date;
run; 
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 144 views
  • 0 likes
  • 2 in conversation