How to merge two data sets below so that for each transaction_date (each transaction) in data set 2 have data of Ret (Data set 1) for 20 days ahead after transaction_date?
data set 1:
Date Cusip8 Ticker Ret
1/1/2003 00036020 AAON 0.0001
1/2/2003 00036020 AAON 0.002
1/3/2003 00036020 AAON 0.00001
1/4/2003 00036020 AAON 0.0005
1/5/2003 00036020 AAON 0.0003
1/8/2003 00036020 AAON 0.0001
1/9/2003 00036020 AAON 0.0001
1/10/2003 00036020 AAON 0.0002
1/11/2003 00036020 AAON 0.00045
1/12/2003 00036020 AAON 0.0001
1/15/2003 00036020 AAON 0.0001
1/16/2003 00036020 AAON 0.0001
1/17/2003 00036020 AAON 0.0032
1/18/2003 00036020 AAON 0.0001
1/19/2003 00036020 AAON 0.00005
1/22/2003 00036020 AAON 0.0001
1/1/2007 00036020 AIR 0.0001
1/2/2007 00036020 AIR 0.002
1/3/2007 00036020 AIR 0.00001
1/4/2007 00036020 AIR 0.0005
1/5/2007 00036020 AIR 0.0003
1/8/2007 00036020 AIR 0.0001
1/9/2007 00036020 AIR 0.002
1/10/2007 00036020 AIR 0.00001
1/11/2007 00036020 AIR 0.0005
1/12/2007 00036020 AIR 0.0003
1/15/2007 00036020 AIR 0.0001
1/16/2007 00036020 AIR 0.002
1/17/2007 00036020 AIR 0.00001
1/18/2007 00036020 AIR 0.0005
1/19/2007 00036020 AIR 0.0003
1/22/2007 00036020 AIR 0.0001
Data set 2:
Transaction_Date cusip8 Ticker Insider Buy/Sell
1/6/2003 00036020 AAON AA Buy
1/6/2003 00036020 AAON AB Sell
1/6/2003 00036020 AAON AC Sell
1/10/2007 00036110 AIR BA Sell
Thanks in advance
This can be done using proc sql join by specifying the condition.
Eg
proc sql;
create table x as
select a.*, b.*
from a, b
where a.date1 < b.date2
and a.date1 > (b.date2 - 20);
quit;
Hi DMoovendhan,
I tried to figure out whether the coding u gave works perfectly. There is a problem. For each transaction, it should have 20 numbers of RET, shouldn't it? But it got much more than that. Based on my real data, the range of time is 180 days. I need to extract RET data (data set 1) for each transaction during 180 days after transaction date (data set 2). How to resolve this problem?
Thanks in advance
You didn't post what output you need yet .
I only wanna merge those two data sets, however each data transaction from dataset 2 should have RET for 20 days from dataset 1 after transaction_date (dataset 2).
I think you already have gotten the answer .
proc sql;
create table x as
select a.*, b.*
from a, b
where a.date1 between b.date2 and b.date2+20;
quit;
I've tried the coding. The result is similar with the first coding. For only 20 days ahead from transaction date, the observations become 54693 days. I think, we need to limit it based on the firm.
post the output you want .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.