Hello, I want to calculate the standard deviation (std) of return on assets (roaq) and cash flows (cfq) based on quarterly data. Assume that below is dataset (my data have about 700,000 observations), the calculation of the standard deviation of ROA and CF for the year 2015 will use data from 2011 to 2015 (5 years * 4 quarters), std of ROA and CF for the year 2016 will use data from 2012 to 2016. I need only yearly std based on 5-year quarterly data. I dont need quarterly std. GVKEY DATADATE FYEARQ FQTR roaq CFQ 1004 20110831 2011 1 0.00947287 0.016586 1004 20111130 2011 2 0.00965134 0.016939 1004 20120229 2011 3 0.00930643 0.016289 1004 20120531 2011 4 0.00586568 0.023345 1004 20120831 2012 1 0.00838594 0.018386 1004 20121130 2012 2 0.00813045 0.018555 1004 20130228 2012 3 0.0082995 0.013811 1004 20130531 2012 4 0.00028078 0.025198 1004 20130831 2013 1 0.00840691 0.019506 1004 20131130 2013 2 0.00811726 0.019542 1004 20140228 2013 3 0.00763635 0.018812 1004 20140531 2013 4 0.00677427 0.017701 1004 20140831 2014 1 0.0053505 0.012474 1004 20141130 2014 2 0.00623962 0.012711 1004 20150228 2014 3 0.00089043 0.007927 1004 20150531 2014 4 -0.0541914 -0.00776 1004 20150831 2015 1 0.00478283 0.014969 1004 20151130 2015 2 0.00599505 0.015947 1004 20160229 2015 3 0.0065046 0.016882 1004 20160531 2015 4 0.00818251 0.018592 When I calculated std based on annual data, I used the following macro, which works well (where firmyear = gvkey+fyear). Is there anyway to modify this macro so that it can use quarterly data rather than yearly data. I prefer this macro as it is efficient. %macro roastdev(dsin=funda, dsout=funda); proc sql; create table comp_roa1 as select a.firmyear, b.roa, b.firmyear as prev_firmyear from &dsin a, &dsin b where a.gvkey = b.gvkey and a.fyear - 4 <= b.fyear <= a.fyear; quit; proc sort data = comp_roa1; by firmyear;run; /* compute stddev */ proc means data=comp_roa1 noprint; output out=comp_roa2 std=/autoname; var roa; by firmyear ; run; /* dataset with standard deviation of roa: firmyear, roa_stddev require at least 4 obs */ data comp_roa2; set comp_roa2; if _freq_ >= 4; run; /* create output dataset */ proc sql; create table &dsout as select a.*, b.roa_stddev from &dsin a left join comp_roa2 b on a.firmyear = b.firmyear; quit; %mend; %roastdev(dsin=work.funda, dsout=funda); proc datasets library=work; delete comp_roa1 comp_roa2; run; Thank you very much! Best regards, Thierry
... View more