topic standard deviation for quarterly data in SAS Procedures

standard deviation for quarterly data

mspak — Mon, 27 Feb 2012 01:09:21 GMT

Good days to all,

I have a variable, return on assets (ROA) financial years 2003 to 2010 for US firms. I would like to calculate performance volatility (=standard deviation of ROA for 3 years) using quarterly data. It does mean that there will be (4 qtrs x 3 years 😃 maximum ROA of 12 quarters for the calculation of standard deviation.

fyearq = financial year

fqtr = quarter of the financial year (there are 4 quarters per annum)

tic = ticker number (company identification codes)

datafqtr = financial year + quarter

ROA = return on asset

I would like to calculate performance volatility for the following years:

year 2003 = standard deviation of ROA in 3 years (overlapping period 2003 - 2005)

year 2004 = standard deviation of ROA in 3 years (overlapping period 2004 - 2006)

year 2005 = standard deviation of ROA in 3 years (overlapping period 2005 - 2007)

year 2006 = standard deviation of ROA in 3 years (overlapping period 2006 - 2008)

year 2007 = standard deviation of ROA in 3 years (overlapping period 2007 - 2009)

year 2008 = standard deviation of ROA in 3 years (overlapping period 2008 - 2010)

additional rules:

1. if there is missing value is found in between/at the middle of the overlapping period, the standard deviation calculation shouldn't be affected (ie. the standard deviation value should be derived).

2. If missing value is found at the begining/ending of the year, the standard deviation calculation should be ignored (ie. should not be derived).

Thank you and hope to get some valuable response.

mspak

standard deviation for quarterly data

art297 — Mon, 27 Feb 2012 01:21:15 GMT

You can always use the intnx function to get the starting and ending dates (see: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212700.htm )

and set the value to missing if you don't have a non-missing value for those dates.

proc summary might be the easiest way to do the calculations and will ignore missing values.

standard deviation for quarterly data

mspak — Mon, 27 Feb 2012 01:27:01 GMT

Thanks,

Can proc expand helpful? Can SAS read the id as YYYYQQ?

I used proc expand for calculation of lag values. I know this function can calculate the lead values too.

I also think about another way - using FIRST. and LAST. function. But I do not know how this function apply to both tic and quarter?

Thank you.

mspak

standard deviation for quarterly data

art297 — Mon, 27 Feb 2012 01:28:45 GMT

Why would you need it? If you are going to exclude missing values, why would you want to replace them with interpolated values?

standard deviation for quarterly data

mspak — Mon, 27 Feb 2012 01:37:07 GMT

Hi again,

I wish to calculate the standard deviation of ROA for a given firm for 3 years period. As all the ROA are arranged in a column, I must identify the first and last ROA used to calculate the standard deviation. Therefore, I feel that FIRST. and LAST. might be useful for data cleaning before the SD calculation.

Or I also consider to arrange the ROA in (8 years x 4 qtrs) 32 columns using proc expand for easy calculation of SD.

standard deviation for quarterly data

art297 — Mon, 27 Feb 2012 02:01:10 GMT

or simply select records between x and y. According to your specs, if you don't have a record for both x and y, then you are not supposed to calculate the SD.

standard deviation for quarterly data

PGStats — Mon, 27 Feb 2012 02:08:49 GMT

This query does the trick: it excludes all cases where at least one of the missing ROA values occurs on a first quarter (fqtr=1) . Otherwise, it calculates the standard deviations from all non-missing ROA values. There are 33661 std values returned.

data beginnings;
input begYear @@;
datalines;
2003 2004 2005 2006 2007 2008
;

proc sql;
create table Roa2Std as
select tic, begYear, std(ROA) as RoaStd
from fPath.Roa2, beginnings
where fyearq between begYear and begYear+2
group by tic, begYear
having sum(ROA is not missing or fqtr ne 1) = 12;

Re: standard deviation for quarterly data

zilong567 — Sat, 30 Jun 2018 08:24:22 GMT

proc sql;
create table exe_ccn_lancmpcrsp as
select a.*,std(b.RET)*sqrt(250) as lstkvol "lag annualized stock volitality"
from exe_ccn_lancmpllq a left join crspa.DSF b
on a.lpermno=b.permno and b.DATE between a.lfybegdt and a.lfyenddt
group by a.lpermno,a.fyear
having sum(b.RET is not missing)>1;
quit;

I use the same method as suggest to obtain stock volitality from crsp daily stock file. But my problem is that the observations go from 36 thousand to more than 1 billion. And there are many same results, with same lpermno, same fyear and same lstkvol. I couldn't find the reason. Thanks!