BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ayin
Quartz | Level 8
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?

1 ACCEPTED SOLUTION

Accepted Solutions
ayin
Quartz | Level 8
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

3 REPLIES 3
Jagadishkatam
Amethyst | Level 16

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
ayin
Quartz | Level 8

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
Quartz | Level 8
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; 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

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.

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
  • 3 replies
  • 1857 views
  • 0 likes
  • 2 in conversation