topic Re: Working With Dates in SAS Procedures

Working With Dates

jrdykstr93 — Tue, 03 Jul 2018 16:31:02 GMT

Hi All,

I have a question on restricting dates. I have columns like so -- "Date", "Price", "Tflag". Wherever the "Tflag" column = '***', I would like to take the range of T+2 and T-2 for date. What i did originally was create lags(one ascending, one descending) of Date and created two new variables Date1 and Date2. This created the correct range on each tflag = ***. But i cannot figure out how to restrict my Date column by this range I created. Also, it is important to note that we are dealing with Trading Days and not just standard days. I say this because if a Tflag is on 6/25/2018 then the t-2 day would be 6/21/2018 and t+2 would be 6/27/2018. This would encompass a total of five dates, which it always should be unless there is significant days in a row.

Thanks.

Re: Working With Dates

Reeza — Tue, 03 Jul 2018 16:33:03 GMT

Have you looked at INTNX with the WEEKDAY option? Though you may want to create your own calendar to account for the unique calendar.

Otherwise, a SQL self join usually works, and/or using PROC EXPAND to calculate a moving statistics with the CONVERT statement.

@jrdykstr93 wrote:

Hi All,

I have a question on restricting dates. I have columns like so -- "Date", "Price", "Tflag". Wherever the "Tflag" column = '***', I would like to take the range of T+2 and T-2 for date. What i did originally was create lags(one ascending, one descending) of Date and created two new variables Date1 and Date2. This created the correct range on each tflag = ***. But i cannot figure out how to restrict my Date column by this range I created. Also, it is important to note that we are dealing with Trading Days and not just standard days. I say this because if a Tflag is on 6/25/2018 then the t-2 day would be 6/21/2018 and t+2 would be 6/27/2018. This would encompass a total of five dates, which it always should be unless there is significant days in a row.

Thanks.

Re: Working With Dates

Astounding — Tue, 03 Jul 2018 16:54:18 GMT

You have omitted critical information.

How do you want to account for holidays? SAS can detect weekdays, but you need to somehow supply the holiday information.

What is the order to the observations? Do the observations you want to select fall both before and after the observation where tflag is *** ?

Re: Working With Dates

jrdykstr93 — Tue, 03 Jul 2018 17:31:54 GMT

Its only trading days. So holidays are excluded. The subset of observations I would like to select fall T-2 and T+2 based on what date there is a tflag = '***'.

Re: Working With Dates

jrdykstr93 — Tue, 03 Jul 2018 17:34:33 GMT

My SQL knowledge is embarrassing. But I will look into proc expand. Thanks.l

Re: Working With Dates

Astounding — Tue, 03 Jul 2018 18:12:01 GMT

So if T-1 is a holiday, you still want T-2 to T+2? Not T-3 to T+2?