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.
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.
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.
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.
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.