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?
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;
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;
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).
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;
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.