topic Re: Calculate standard deviation over fiscal year in SAS Data Management

Calculate standard deviation over fiscal year

JOLSAS — Sun, 28 Feb 2016 20:36:36 GMT

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!

Re: Calculate standard deviation over fiscal year

TomKari — Sun, 28 Feb 2016 23:45:44 GMT

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

Re: Calculate standard deviation over fiscal year

Ksharp — Mon, 29 Feb 2016 02:00:19 GMT

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;

Re: Calculate standard deviation over fiscal year

JOLSAS — Mon, 29 Feb 2016 18:38:19 GMT

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!

Re: Calculate standard deviation over fiscal year

JOLSAS — Mon, 29 Feb 2016 20:10:12 GMT

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