topic How to merge two data sets with specific range of time following a specific date in SAS Enterprise Guide

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

acil — Thu, 20 Nov 2014 04:32:58 GMT

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

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

DMoovendhan — Thu, 20 Nov 2014 08:08:36 GMT

This can be done using proc sql join by specifying the condition.

proc sql;

create table x as

select a.*, b.*

from a, b

where a.date1 < b.date2

and a.date1 > (b.date2 - 20);

quit;

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

acil — Sun, 23 Nov 2014 09:54:42 GMT

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

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

Ksharp — Sun, 23 Nov 2014 11:31:34 GMT

You didn't post what output you need yet .

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

acil — Sun, 23 Nov 2014 12:23:41 GMT

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

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

Ksharp — Mon, 24 Nov 2014 12:03:24 GMT

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;

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

acil — Mon, 24 Nov 2014 14:38:19 GMT

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.

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

Ksharp — Tue, 25 Nov 2014 12:19:35 GMT

post the output you want .