topic How to join/merge tables (vlookup) not based on exact match? in SAS Enterprise Guide

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

ayin — Mon, 17 Jul 2017 03:34:39 GMT

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?

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

Jagadishkatam — Mon, 17 Jul 2017 03:46:47 GMT

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;

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

ayin — Mon, 17 Jul 2017 04:44:38 GMT

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

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

ayin — Mon, 17 Jul 2017 07:17:03 GMT

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;