BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hx
Calcite | Level 5 hx
Calcite | Level 5
table 1dateprice
company11/1/200010
company21/1/200011
table2dateprice
company112/31/199910.1
company11/1/200010
company11/2/200010.2
company11/3/200010.1
company11/4/200010.3
company212/31/199911.2
company21/1/200011
company21/2/200011.2
company21/3/200010
company21/4/200011.3
table3dateprice
company112/31/199910.1
company11/1/200010
company11/2/200010.2
company212/31/199911.2
company21/1/200011
company21/2/200011.2

Hi,

I have datasets that look like table1 and table2, the result I want is table3. That is, the data I need is 1 lag date of the price and 1 day after. Can someone help me with this in SAS?

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20


data one;
input company :$10.	date :mmddyy10.	price ;
format date mmddyy10.;
cards;
company1	1/1/2000	10
company2	1/1/2000	11
;

data two;
input company :$10.	date :mmddyy10.	price ;
format date mmddyy10.;
cards;
company1	12/31/1999	10.1
company1	1/1/2000	10
company1	1/2/2000	10.2
company1	1/3/2000	10.1
company1	1/4/2000	10.3
company2	12/31/1999	11.2
company2	1/1/2000	11
company2	1/2/2000	11.2
company2	1/3/2000	10
company2	1/4/2000	11.3
;

proc sql;
create table want as
select a.company,b.date,b.price
from 
one a left join two b
on a.company=b.company and  a.date-1<=b.date<=a.date+1
order by a.company,b.date;
quit;

View solution in original post

1 REPLY 1
novinosrin
Tourmaline | Level 20


data one;
input company :$10.	date :mmddyy10.	price ;
format date mmddyy10.;
cards;
company1	1/1/2000	10
company2	1/1/2000	11
;

data two;
input company :$10.	date :mmddyy10.	price ;
format date mmddyy10.;
cards;
company1	12/31/1999	10.1
company1	1/1/2000	10
company1	1/2/2000	10.2
company1	1/3/2000	10.1
company1	1/4/2000	10.3
company2	12/31/1999	11.2
company2	1/1/2000	11
company2	1/2/2000	11.2
company2	1/3/2000	10
company2	1/4/2000	11.3
;

proc sql;
create table want as
select a.company,b.date,b.price
from 
one a left join two b
on a.company=b.company and  a.date-1<=b.date<=a.date+1
order by a.company,b.date;
quit;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 649 views
  • 0 likes
  • 2 in conversation