topic Matching CRSP Daily with non-trading days in SAS Programming

Matching CRSP Daily with non-trading days

JKCho — Tue, 16 Jul 2019 22:08:09 GMT

Hi All...

I have asked similar questions but cannot get the right answer yet. I thought I got the answer... but realized it is not.

CRSP Daily data only has trading dates( no holidays, no Saturdays, No Sundays).

I have other data(let me call it OTHER) that has just random calendar dates.

If I want to merge these two, I inevitably lose some non-trading days that are in OTHER.

Rather losing OTHER's date, I want to keep them by matching the closest trading dates.

For example, 2019/07/14 (it is Sunday) will be matched with 2019/07/15.

In other words, I do not want to lose any data in OTHER because of not matching dates.

Can anyone know this kind of code that matched the closet available date?

Tons of Thanks!

Jerry

Re: Matching CRSP Daily with non-trading days

ballardw — Tue, 16 Jul 2019 22:11:34 GMT

Example input data for BOTH data sets.

Example of the desired result for those data sets.

Include at least one example of each type of match involved

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Matching CRSP Daily with non-trading days

ChrisNZ — Wed, 17 Jul 2019 01:42:26 GMT

This means you'll have duplicates from CRSP when they match equal OTHER date and they match closest OTHER date.

Is this what you want?

If you have two possible closest matches (day before and day after), what do you do?

In other words and as stated, please provide usable examples.

Re: Matching CRSP Monthly with non-trading days

JKCho — Wed, 17 Jul 2019 15:36:25 GMT

proc sql;
create table esgprice37 as
select a.* , b.*
from CRSPnew a
inner join esgtest4 b
on a.cusip = b.cusip8 and b.DATADATE13 - a.DATE >=0
having (b.DATADATE37 - a.DATE)=min(b.DATADATE37 - a.DATE);
quit;

oh... my bad.

I need the closest 'next' day.

I will provide examples.

By the way, using the above code only provided outcomes that two dates are exactly matched (datadate37=date).

For example, when the DATADATE37 is 2012/03/28, my code does not match this date with 2012/03/31 from CRSP Monthly(I use monthly data now for convenience. the data I attach is also monthly data).

Rather, my code deletes all kinds of this unmatched dates and output the exactly matched dates only... (DATADATE37 = date).

The intention of the code below is not to delete unmatched data. But my outcome is not like that.

having (b.DATADATE37 - a.DATE)=min(b.DATADATE37 - a.DATE);

Please have a look on my data files and try the first above code and please look through the outcome. The output data missed almost half of the original observations. I referred to other posts in the community and the WRDS's article. BUT theire codes are the same as mine I used.

Re: Matching CRSP Monthly with non-trading days

JKCho — Wed, 17 Jul 2019 15:38:23 GMT

Yes. The CRSP file is too big to upload. Let me make a very simplified file

Re: Matching CRSP Daily with non-trading days

JKCho — Wed, 17 Jul 2019 16:21:41 GMT

I attach very simply files.

What I want is... just as below. Because of the discrepancy between Trading and Calender day, I used the code

"having (a.DATE - b.d_9 )=min(a.DATE - b.d_9 );"

This code is not working somehow...

Re: Matching CRSP Daily with non-trading days

JKCho — Wed, 17 Jul 2019 16:47:19 GMT

https://communities.sas.com/t5/SAS-Enterprise-Guide/The-execution-of-this-query-involves-performing-one-or-more/td-p/443275

To all(specifically SAS Rookies like me),

the problem that I lost the output is due to missing Group by~~~.

Plz, refer to the above link if you lose the output observations and have NOTE like Cartesian products blah blah~~