topic count the number of trading days in the past 3 months in SAS Programming

count the number of trading days in the past 3 months

Nieves — Tue, 21 Aug 2018 03:31:53 GMT

Hello,

How to count the number of trading days in the past 3 months, using CRSP daily data ?

Thanks

Re: count the number of trading days in the past 3 months

mkeintz — Tue, 21 Aug 2018 03:59:16 GMT

You're extremely lucky to have a WRDS consultant on this forum - me.

I take it you really mean the number of days the market was active, rather than the number of days a particular stock traded, right?

What does "last 3 months" mean? Last three calendar months? If the current trading date is in the middle of August, then do you want the number of trading days in May, June, and July?

Re: count the number of trading days in the past 3 months

Nieves — Tue, 21 Aug 2018 05:09:18 GMT

Hi @mkeintz

Nice to hear from a wrds expert. I am calculating the sigma ratio defined as follows: daily variation of returns computed as an annualized 3-month rolling sample standard deviation, using daily stock price data from CRSP.

If there are fewer than five nonzero observations over the 3 months used in the rolling window computation, SIGMA will be counted as missing and replace with the cross-sectional mean of SIGMA.

Therefore, I need the number of days a particular stock traded, rather than the number of days the market was active.

So for each date t, I need to calculate the number of trading days for each stock for the past three calendar month of the date t.

Yes. If the current trading date is in the middle of August, I want the number of trading days in May, June, and July for each stock in the data.

Looking forward to your help 🙂

Re: count the number of trading days in the past 3 months

mkeintz — Tue, 21 Aug 2018 17:04:33 GMT

OK, I get that r-square sub (i,k) is r-square for the i'th stock and k'th month. But what is the model from which each monthly r-square is calculated? Is it the capm value or something else? Where are you getting the r-square?

More generally, regarding you initial question, here's how to get the number of trading days in the prior three calendar months:

data want;
  set dsf;
  by permno;
  retain trdays_3months;

  if dif(month(date))^=0 or first.permno=1 then trdays_3months=ifn(dif3(permno)=0,dif3(_n_),.);
run;

This program:

Assumes that the daily stock file is sorted by permno, date.
The "dif(x)" function is defined as x-lag(x). So the if clause tests whether the record in hand is the beginning of a new month, or beginning of a new permno.
The "then" clause calculates the dif3(_n_), same as _n_-lag3(_n_), which means the current observation number minus the observation number three instances ago. I say three instances ago instead of three observations ago because the dif3 function (like the lag3 function) is a queue-management function, which is updated only when the IF clause is true. This means it's the difference in _N_ between current record and the 3rd prior time a month change was encountered. And _N_ in this program is the observation number. I.e, this is NOT like a look back x rows you might do in Excel.

However you don't want to contaminate the result of this statement when processing the first 3 months of a new permno. So there is a test of dif3(permno) such that a missing value is assigned to trdays_3months during the first 3 months of a new permno.
The "retain trdays_3months" statement prevents SAS from automatically resetting new variables to a missing at the start of each iteration of the data step.

Edited additional note:

This program assume that each stock has at least one trading day per month. This is, if a stock is delisted for at least one month, then the period after relisting will have erroneous values for the first 3 months.

Re: count the number of trading days in the past 3 months

Nieves — Wed, 22 Aug 2018 04:12:17 GMT

Hi @mkeintz

Thank you very much for your kind help and detailed advice. Your program works!

The return here is the (price-price_lag)/price_lag

In addition to your program, may I also ask how to implement the additional requirement ?

if there are fewer than five nonzero observations over the prior 3 calendar months used in the rolling window computation (which is

trdays_3months < 5)

, SIGMA will be counted as missing and replace with the cross-sectional mean of SIGMA. I have already calculated SIGMA in my data and set as missing if less than 5 nonzero observations over the prior 3 calendar months.

Thanks again 🙂

Re: count the number of trading days in the past 3 months

mkeintz — Thu, 23 Aug 2018 15:02:32 GMT

Regarding setting sigma to missing when trdays_3months<5, I wouldn't shift implementing that rule to the calculation of trdays_3months. I'd keep it right where you apparently have it - when you calculate sigma. BUT ... you can do that in the same step as in this code:

data want (drop=m);
  set dsf;
  by permno;
  retain trdays_3months 0  m 0;

  /* To hold three separate sums of monthly squared returns */
  array mretsq{0:2} _temporary_  (3*0);

  if dif(month(date))^=0 or first.permno=1 then do;
    trdays_3months=ifn(dif3(permno)=0,dif3(_n_),.);

    retsquare_3months=sum(of mretsq{*});
    if trdays_3months>=5 then sigma=sqrt( (252/(N-1))*retsquare_3months  );

    /* Point to next element of array mretsq, and zero it out */
    m=mod(m+1,3);
    mretsq{m}=0;
  end;

  mretsq{m}+ret**2;

run;

This code declare a 3-element temporary array MRETSQ, to hold sum-of-squared-returns for each of the last three months. It's a temporary array, so (1) no new variables are output to the new data set, and (2) values are automatically retained for record to record. The variable m is used to identify which element of the array to add the current record (element 0, element 1, or element 2). But the actual adding of the current record to the monthly total takes place AFTER the code for discovering a change in month. That's because you want to generate sigma for prior three months without contamination from the current record (i.e. the first record of the new month).

So the if/then statement is now the same IF test, but has a "then do" group instead of a single "then" action. The do group includes calculation of two new variables: retsquare_3months, and sigma. And it also rotates M to the next mretsq element, by using the mod function.