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

[ayin]

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?

[Jagadishkatam]

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;

[ayin]

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

[ayin · Accepted solution]

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;