Desktop productivity for business analysts and programmers

How to merge two data sets with specific range of time following a specific date

Reply
Occasional Contributor
Posts: 8

How to merge two data sets with specific range of time following a specific date

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

Contributor
Posts: 61

Re: How to merge two data sets with specific range of time following a specific date


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;

Occasional Contributor
Posts: 8

Re: How to merge two data sets with specific range of time following a specific date

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

Super User
Posts: 9,875

Re: How to merge two data sets with specific range of time following a specific date

You didn't post what output you need yet .

Occasional Contributor
Posts: 8

Re: How to merge two data sets with specific range of time following a specific date

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

Super User
Posts: 9,875

Re: How to merge two data sets with specific range of time following a specific date

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;

Occasional Contributor
Posts: 8

Re: How to merge two data sets with specific range of time following a specific date

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.

Super User
Posts: 9,875

Re: How to merge two data sets with specific range of time following a specific date

post the output you want .

Ask a Question
Discussion stats
  • 7 replies
  • 493 views
  • 0 likes
  • 3 in conversation