BookmarkSubscribeRSS Feed
acil
Calcite | Level 5

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

7 REPLIES 7
DMoovendhan
Quartz | Level 8


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;

acil
Calcite | Level 5

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

Ksharp
Super User

You didn't post what output you need yet .

acil
Calcite | Level 5

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).

Ksharp
Super User

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;

acil
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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