Ranging by closest dates

[JKCho]

proc summary data=crspsdc1 nway;
where intck("day",Date, filing_Date) between -1 and 2;
class cusip filing_Date;
var price;
output out=want std=priceStd;
run;

Hi all!!

the above code is for having ranges and calculating std of the price. The code is good but I need sth more, obtaining business days only. Since filing dates are random and I need the range from filing data-1 to filing date+2, either or both filing data-1 and filing date+2 can be non-business days, which turns different _FREQ_ in the outcome from the code. The variable DATE only contains biz days so the code above could not count non-biz days and resulted in different FREQs in the WANT file...

So, I have been thinking the code finding closest business days from DATE variables. (DATE variables only contains biz days)

I know like having (a.XX - b.XX) = min (a.XX - b.XX) but this doesn't look working this case (this is useful for matching only) and have no idea how to create the BUSINESS DAY ONLY RANGE...

It seems that there is a widely-used code for this business day only interval as I can see this a lot when I read finance and accounting papers but hard to make the code...

I appreciate any of your comment!

[Reeza]

WEEKDAY() is readily available but business days are not. As I mentioned in previous threads you'll need to calculate a custom calendar with only trading dates. Not sure if you need to account for multiple jurisdictions as well, if for example you have trading data for companies in Canada or US which may have different holidays.

[JKCho]

Hi Reeza,

Yes. I know the WEEKDAY statement and now have trading dates only data(That is my DATE variable). As you mentioned WEEKDAY does not count other nontrading days, I am thinking of a new coding that can solve the question. What I struggle on is how to range these trading dates with the filing_date thee...

I already deleted the Canadian data for the ease of analysis.

[Reeza · Accepted solution]

https://communities.sas.com/t5/SAS-Programming/Finding-the-next-working-day-with-a-custom-calendar/m...

https://communities.sas.com/t5/SAS-Enterprise-Guide/Loop-is-not-considering-the-condition/td-p/13687...

Honestly, this is a non-trivial task, part of why SAS doesn't have an automatic function to do it and why there isn't a full/clear solution that someone will code up for you quickly - it's not a quick process and it does take some testing and time to get it working correctly. The benefit of creating a custom calendar, as noted in the threads, is the ability to use INTNX or INTCK() functions which make the remaining steps simpler. 

This post is probably the best one to use as a starting point for your actual work, but I highly recommend the other threads first to understand some of the complexity of this problem. 

https://communities.sas.com/t5/SAS-Communities-Library/SAS-Tip-Generating-Holiday-Lists/ta-p/557799?...

---

@JKCho wrote:

Hi Reeza,

Yes. I know the WEEKDAY statement and now have trading dates only data(That is my DATE variable). As you mentioned WEEKDAY does not count other nontrading days, I am thinking of a new coding that can solve the question. What I struggle on is how to range these trading dates with the filing_date thee...

I already deleted the Canadian data for the ease of analysis.

---

[Reeza]

Will you always have data for those windows?

If you can just take the records nearby based on record numbers rather than dates this is a much simpler problem to handle. 

ie if your range (Date +/- 3 days) will have data for each of the 3 days on either side and not be missing data you can use that feature. 

the above code is for having ranges and calculating std of the price. The code is good but I need sth more, obtaining business days only. Since filing dates are random and I need the range from filing data-1 to filing date+2, either or both filing data-1 and filing date+2 can be non-business days, which turns different _FREQ_ in the outcome from the code. The variable DATE only contains biz days so the code above could not count non-biz days and resulted in different FREQs in the WANT file...

[JKCho]

Oh.. I got your meaning of custom calendar... You did mean I need to have a function that skips customed holidays so that my code can only follow trading days. Yeah... This is more of hard jobs..

Since CRSP data is for trading days only and this is based on dates I have no way... I can convert to numbers but it must no be continuous integers but 4001 then 4004 if there are Saturday and Sunday in-between.

Anyway, I appreciate your link! I have to work on these more!

[Reeza]

Yeah, the building of the calendar is tedious but not that painful and then it becomes quite powerful. Using a custom calendar within SAS ETS procs will allow you to calculate moving averages with ease AND it's a lot easier to be sure your code is correct, rather than continuously testing each section where you're trying to manually code around the issue.