topic Re: How to match using date identifier in SAS Programming

How to match using date identifier

JKCho — Sun, 14 Jul 2019 04:10:14 GMT

proc sql;
  select HAVE.FRIMID, COMP_T, CRSP_T, ASSET, RETURN 
  from HAVE, CRSP
  where HAVE.date =CRSP.(date+1year or +1month) and HAVE.ID =CRSP.ID
quit;

This code just represents what I want...

you can see

HAVE.date =CRSP.(date+1year)

What I am doing now is to calculate annual return starting from a specific date. e.g. 2019/10/31~2020/10/31. To do this, I need to have prices now and a year later. However, I do not know how to match. Since it is not just a number but year... +1 does not work.

Please help me how can I add day/month/year on my identifiers when they are calendar dates...

I first made code to transform that YYMMDD to YYMM and then +1. but it sometimes does not make sense as if the case is 2013/12 then +1 is 2013/13... or 2013/10/31 became 2013/10/32... instead 2013/11/01.

Because I need to match same to same+@ instead same to same, I am struggling with this.

Please help me. I searched for other posts but haven't found similar.

Re: How to match using date identifier

Kurt_Bremser — Sun, 14 Jul 2019 05:29:51 GMT

Use the intnx() function to calculate date intervals.

Re: How to match using date identifier

Reeza — Sun, 14 Jul 2019 20:48:52 GMT

SAS considers dates as numbers so you can just add numbers to get what you want. The trick is accounting for months and years which are not consistent, and the ending of a year, as you've already noticed.

So SAS has a function INTNX() that allows you increment dates instead. However, to use it you must have a SAS date, which is a numeric variable (variable type) with a date format applied.

Re: How to match using date identifier

JKCho — Mon, 15 Jul 2019 03:14:48 GMT

Thank you Reeza!!
I am pretty sure how intnx or intck.
Before doing my works, I just want to ask a quick question. INTNX() just increments dates, months, or years but does not check whether these incremented dates are trading days or not. If so, I need to add another code to match my data properly and more precisely by like this..
having (b.trading-a.non_trading)=min(b.trading-a.non_trading);

Re: How to match using date identifier

Reeza — Mon, 15 Jul 2019 03:54:22 GMT

If you need to deal with trading dates you need to create a custom calendar. You'll also need to consider if stocks are in other countries and follow other calendars.

Re: How to match using date identifier

Reeza — Mon, 15 Jul 2019 04:04:43 GMT

You can use the WEEKDAY interval to get closer by the way, that only counts weekdays.