Calculate standard deviation over fiscal year

[JOLSAS]

Hi all,

I have a dataset about firm stock monthly returns that looks like the following:

firmID              date              return            fyr

1                    20100131      0.0003           9

1                    20100228      0.0001           9

.......................

1                    20101231     0.0012            9

1                    20110131      -0.0015          9

.........................

2                    20110430     -0.1201          12

2                    20110531      -0.0008         12

2                    20110630      0.0154          12

...........................

2                    20151231     0.0087           12

3                    19960531     0.0789           5

3                    19960630     0.1023           5

...............................

3                     20141231    -0.1234          5

I have over one-thousand firms, and these firms may have data up to 30 years. I have one return for each pair of firm-date, and these returns are monthly. fyr is the fiscal year-end. I want to calculate standard deviation for monthly return over the past fiscal year. In other words:

For firm 1, I want to calculate:

2010: standard deviation of (2009 Oct. return, 2009 Nov. return, 2009 Dec. return, 2010 Jan. return, ....., 2010 Sept. return)

2011: standard deviation of (2010 Oct. return, 2010 Nov. return, 2010 Dec. return, 2011 Jan. return, ....., 2011 Sept. return)

......

For firm 2, I want to calculate:

2011: standard deviation of (2011 Jan. return, 2011 Feb. return, 2011 Mar. return, ....., 2011 Dec. return)

2012

2013

..........

For firm 3, I want to calculate:

1996: standard deviation of (1995 Jun. return, 1995 Jul. return, ...., 1995 Dec. return, 1996, jan. return,  ....., 1996 Apr. return)

1997

1998

..............

So the output will look something like this:

firmID         year          std_ret

1               2010           0.20394

1               2011           1.23324

............

2              2011            0.234234

2              2012            1.234

.............

2              2015            3.34732

3              1996            2.34234

..............

3               2014            1.2345

I might have missing values on return. So I might not have the full 12-month data on each pair of firm-year. I want to restrict SAS to calculate only if I have at least 7 months out of 12 months. If there is not sufficient data, then SAS should drop this firm-year.

This is not a rolling standard deviation as I only use each line once. However for different firms, the calculation will be different as I start and end at a different month.

Please help! This seems difficult for me. Any suggestion is greatly appreicated. Thank you!

[TomKari · Accepted solution]

Nice problem!

I think this is a useful part of it. I've slightly renamed date to InDate because I don't like using variable names that are language elements of SAS. (past painful experience)

If you run this code:

data part1;

set have;

InYear = year(InDate);

FiscalEnd = mdy(FYr, 1, InYear);

FiscalEnd = intnx('month', FiscalEnd, 0, 'end');

if InDate <= FiscalEnd

then FiscalYear = InYear - 1;

else FiscalYear = InYear;

run;

FiscalEnd should end up as the end of the fiscal year for the year part of date; ie for your first line, it should be the last day of September 2010.

And then comparing your input date to that date you should be able to set the fiscal year to the year in the date, or the year before.

Based on having a fiscal year and a firm id, running the StdDev should be pretty straightforward.

Note - code is very minimally tested.

Tom

[JOLSAS]

Hi Tom,

Thanks for the help! FiscalYear essentially puts all the months I want in a fiscal year under one value of FiscalYear. Ingenious! I ran your code, then added the following, and got what I want. In case anybody needs these in the future:

proc sql;
    create table part2 as select firmID,  std(return) as std_ret, FiscalYear, fyr
    from part1 group by firmID, FiscalYear, fyr;
quit;
data want; set part2;
    if fyr>=6 then fyear=FiscalYear+1; else fyear=FiscalYear;
    drop FiscalYear fyr;
run;

I defined fyear slightly different from FiscalYear because I need to merge the table want with other tables based on firmID and fyear. Problem solved! Thank you very much!

JOL

[Ksharp]

data have;
input firmID              date   : yymmdd10.           return            fyr;
format date    yymmddn. ;
cards;
1                    20100131      0.0003           9
1                    20100228      0.0001           9
1                    20101231     0.0012            9
1                    20110131      -0.0015          9
2                    20110430     -0.1201          12
2                    20110531      -0.0008         12
2                    20110630      0.0154          12
2                    20151231     0.0087           12
3                    19960531     0.0789           5
3                    19960630     0.1023           5
3                     20141231    -0.1234          5
;
run;
proc sql;
create table month_return as
 select firmID,year(date) as year,month(date) as month,sum(return) as month_return
  from have
   group by firmID,calculated year,calculated month;

create table key as
 select distinct firmID,year(date) as year
  from have;
 
create table want as
 select a.*,(select std(month_return) from month_return 
     where (year=a.year-1 and month in (10:12)) or 
           (year=a.year and month in (1:9)) ) as std
  from key as a; 
  
quit;

[JOLSAS]

Hi Keshan,

I appreciate your help on this. Though my sql knowledge is limited, but from what I understood, your code calculates std when fyr=9. Is that correct? However I have firms having fyrs in virtually every month. Would you revise your code to address that? Thanks a lot in advance!