How to create trade date?

[bd_user_10]

Hi Everyone,

I have the following settlement dates in my dataset;

20020514

20020522

20020524

20020527

I would like to find the trade date which is settlement dates -3 days but has to be working day. How to do that? 

Can someone please help? Thanks in advance for your help.

[Kurt_Bremser]

Subtract 3, and then check for Saturday and Sunday:

data want;
set have;
format trade_date yymmddd10.;
trade_date = settlement - 3;
do while (weekday(trade_date) in (1,7));
  trade_date = trade_date - 1;
end;
run;

For more complicated calculations, consider to use the holiday() function.

[bd_user_10]

Hi Kurt, when I am running the code for some days the date format are not changing. Therefore, I am getting correct and incorrect dates both. Any ways to fix that? Also, I think you mean to format the settlement date first, right?

[Kurt_Bremser · Accepted solution]

Since you did not provide a data step with example data, I had to make up my own. I also assume that date values are always stored as SAS dates.

data have;
input settlement :yymmdd8.;
format settlement yymmddd10.;
cards;
20020514
20020522
20020524
20020527
;
run;

data want;
set have;
format trade_date yymmddd10.;
trade_date = settlement - 3;
do while (weekday(trade_date) in (1,7));
  trade_date = trade_date - 1;
end;
run;

proc print data=want noobs;
run;

Result:

settlement    trade_date

2002-05-14    2002-05-10
2002-05-22    2002-05-17
2002-05-24    2002-05-21
2002-05-27    2002-05-24

2002-05-14 was a Tuesday, 2002-05-10 a Friday

2002-05-22 was a Wednesday, so we have to go back one day further

The next two dates were a Friday and a Monday, so going back just 3 hits another weekday.

[bd_user_10]

Thank you very much for clarification! It works fine!

[mkeintz]

Are you saying it's ok to have a trade date on a holiday, as long as that holiday is a Mon through Fri?

And if by trade, you mean trade on a public stock exchange, there are non-holidays closuers, such as 9/11/2001, a Tuesday.  The New York Stock exchange did not open that day, and did not re-open until 9/17.

If either of the above conditions are relevant to your task, you may need to define a calendar using all active trade dates.

[bd_user_10]

Hi mkeintz, thanks for a very good point! How to define such calendar? Could you please help?