Quartz | Level 8

## Calculate standard deviation over fiscal year

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Onyx | Level 15

## Re: Calculate standard deviation over fiscal year

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

4 REPLIES 4
Onyx | Level 15

## Re: Calculate standard deviation over fiscal year

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

Quartz | Level 8

## Re: Calculate standard deviation over fiscal year

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

Super User

## Re: Calculate standard deviation over fiscal year

``````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;``````
Quartz | Level 8

## Re: Calculate standard deviation over fiscal year

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!

Discussion stats
• 4 replies
• 2154 views
• 0 likes
• 3 in conversation