topic Re: sort and extract by table in SAS Programming

sort and extract by table

hx — Fri, 19 Apr 2019 17:07:26 GMT

table 1	date	price
company1	1/1/2000	10
company2	1/1/2000	11

table2	date	price
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

table3	date	price
company1	12/31/1999	10.1
company1	1/1/2000	10
company1	1/2/2000	10.2
company2	12/31/1999	11.2
company2	1/1/2000	11
company2	1/2/2000	11.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?

Re: sort and extract by table

novinosrin — Fri, 19 Apr 2019 17:36:32 GMT



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;