topic Re: How to create trade date? in SAS Programming

How to create trade date?

bd_user_10 — Wed, 24 Oct 2018 08:44:26 GMT

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.

Re: How to create trade date?

Kurt_Bremser — Wed, 24 Oct 2018 08:58:38 GMT

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.

Re: How to create trade date?

bd_user_10 — Wed, 24 Oct 2018 09:59:00 GMT

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?

Re: How to create trade date?

Kurt_Bremser — Wed, 24 Oct 2018 10:26:41 GMT

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.

Re: How to create trade date?

mkeintz — Wed, 24 Oct 2018 13:35:56 GMT

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.

Re: How to create trade date?

bd_user_10 — Thu, 25 Oct 2018 02:53:36 GMT

Thank you very much for clarification! It works fine!

Re: How to create trade date?

bd_user_10 — Thu, 25 Oct 2018 03:12:04 GMT

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