deal all,
i have to compute rolling standard deviation of Variable X of each company for the preceding 12 quarters. i have the panel data of nearly 5000 companies and 37 quarters. i have the quarterly data in the following format.
Company_Name | Quarter | X |
20 Microns Ltd. | Mar-10 | 0.29 |
20 Microns Ltd. | Jun-10 | 0.29 |
20 Microns Ltd. | Sep-10 | 0.29 |
20 Microns Ltd. | Dec-10 | 0.42 |
20 Microns Ltd. | Mar-11 | 0.29 |
20 Microns Ltd. | Jun-11 | 0.29 |
20 Microns Ltd. | Sep-11 | 0.29 |
20 Microns Ltd. | Dec-11 | 0 |
20 Microns Ltd. | Mar-12 | 0 |
20 Microns Ltd. | Jun-12 | 0.77 |
20 Microns Ltd. | Sep-12 | 0.73 |
20 Microns Ltd. | Dec-12 | 0 |
20 Microns Ltd. | Mar-13 | 3.58 |
20 Microns Ltd. | Jun-13 | 1.84 |
20 Microns Ltd. | Sep-13 | 4.88 |
20 Microns Ltd. | Dec-13 | 0 |
20 Microns Ltd. | Mar-14 | 0 |
20 Microns Ltd. | Jun-14 | 0 |
20 Microns Ltd. | Sep-14 | 0 |
20 Microns Ltd. | Dec-14 | 0 |
20 Microns Ltd. | Mar-15 | 0.03 |
20 Microns Ltd. | Jun-15 | 0 |
20 Microns Ltd. | Sep-15 | 0 |
20 Microns Ltd. | Dec-15 | 0 |
20 Microns Ltd. | Mar-16 | 0 |
20 Microns Ltd. | Jun-16 | 0 |
20 Microns Ltd. | Sep-16 | 0 |
20 Microns Ltd. | Dec-16 | 0.11 |
20 Microns Ltd. | Mar-17 | 0.03 |
20 Microns Ltd. | Jun-17 | 1.32 |
20 Microns Ltd. | Sep-17 | 0.08 |
20 Microns Ltd. | Dec-17 | 0.1 |
20 Microns Ltd. | Mar-18 | 0.37 |
20 Microns Ltd. | Jun-18 | 0.09 |
20 Microns Ltd. | Sep-18 | 0.33 |
20 Microns Ltd. | Dec-18 | 0.26 |
20 Microns Ltd. | Mar-19 | 0.11 |
3I Infotech Ltd. | Mar-10 | 39.02 |
3I Infotech Ltd. | Jun-10 | 41.18 |
3I Infotech Ltd. | Sep-10 | 37.33 |
3I Infotech Ltd. | Dec-10 | 40.36 |
3I Infotech Ltd. | Mar-11 | 39.46 |
3I Infotech Ltd. | Jun-11 | 38.8 |
3I Infotech Ltd. | Sep-11 | 36.43 |
3I Infotech Ltd. | Dec-11 | 22.01 |
3I Infotech Ltd. | Mar-12 | 18.04 |
3I Infotech Ltd. | Jun-12 | 57.82 |
3I Infotech Ltd. | Sep-12 | 54.74 |
3I Infotech Ltd. | Dec-12 | 55.02 |
3I Infotech Ltd. | Mar-13 | 55.35 |
i want the output in the following format
Company_Name | Quarter | X | Rolling_mean | Rolling_Std.dev |
20 Microns Ltd. | Mar-10 | 0.29 | ||
20 Microns Ltd. | Jun-10 | 0.29 | ||
20 Microns Ltd. | Sep-10 | 0.29 | ||
20 Microns Ltd. | Dec-10 | 0.42 | ||
20 Microns Ltd. | Mar-11 | 0.29 | ||
20 Microns Ltd. | Jun-11 | 0.29 | ||
20 Microns Ltd. | Sep-11 | 0.29 | ||
20 Microns Ltd. | Dec-11 | 0 | ||
20 Microns Ltd. | Mar-12 | 0 | ||
20 Microns Ltd. | Jun-12 | 0.77 | ||
20 Microns Ltd. | Sep-12 | 0.73 | ||
20 Microns Ltd. | Dec-12 | 0 | ||
20 Microns Ltd. | Mar-13 | 3.58 | 0.56 | 0.94 |
20 Microns Ltd. | Jun-13 | 1.84 | 0.68 | 1.00 |
20 Microns Ltd. | Sep-13 | 4.88 | 1.03 | 1.52 |
20 Microns Ltd. | Dec-13 | 0 | 1.01 | 1.54 |
20 Microns Ltd. | Mar-14 | 0 | 0.97 | 1.56 |
20 Microns Ltd. | Jun-14 | 0 | 0.95 | 1.57 |
20 Microns Ltd. | Sep-14 | 0 | 0.93 | 1.58 |
20 Microns Ltd. | Dec-14 | 0 | 0.91 | 1.59 |
20 Microns Ltd. | Mar-15 | 0.03 | 0.91 | 1.59 |
20 Microns Ltd. | Jun-15 | 0 | 0.91 | 1.59 |
20 Microns Ltd. | Sep-15 | 0 | 0.85 | 1.61 |
20 Microns Ltd. | Dec-15 | 0 | 0.79 | 1.63 |
please suggest a suitable SAS code.
thanks in advance
data have;
input Company_Name $16. Quarter $ X;
cards;
20 Microns Ltd. Mar-10 0.29
20 Microns Ltd. Jun-10 0.29
20 Microns Ltd. Sep-10 0.29
20 Microns Ltd. Dec-10 0.42
20 Microns Ltd. Mar-11 0.29
20 Microns Ltd. Jun-11 0.29
20 Microns Ltd. Sep-11 0.29
20 Microns Ltd. Dec-11 0
20 Microns Ltd. Mar-12 0
20 Microns Ltd. Jun-12 0.77
20 Microns Ltd. Sep-12 0.73
20 Microns Ltd. Dec-12 0
20 Microns Ltd. Mar-13 3.58
20 Microns Ltd. Jun-13 1.84
20 Microns Ltd. Sep-13 4.88
20 Microns Ltd. Dec-13 0
20 Microns Ltd. Mar-14 0
20 Microns Ltd. Jun-14 0
20 Microns Ltd. Sep-14 0
20 Microns Ltd. Dec-14 0
20 Microns Ltd. Mar-15 0.03
20 Microns Ltd. Jun-15 0
20 Microns Ltd. Sep-15 0
20 Microns Ltd. Dec-15 0
20 Microns Ltd. Mar-16 0
20 Microns Ltd. Jun-16 0
20 Microns Ltd. Sep-16 0
20 Microns Ltd. Dec-16 0.11
20 Microns Ltd. Mar-17 0.03
20 Microns Ltd. Jun-17 1.32
20 Microns Ltd. Sep-17 0.08
20 Microns Ltd. Dec-17 0.1
20 Microns Ltd. Mar-18 0.37
20 Microns Ltd. Jun-18 0.09
20 Microns Ltd. Sep-18 0.33
20 Microns Ltd. Dec-18 0.26
20 Microns Ltd. Mar-19 0.11
3I Infotech Ltd. Mar-10 39.02
3I Infotech Ltd. Jun-10 41.18
3I Infotech Ltd. Sep-10 37.33
3I Infotech Ltd. Dec-10 40.36
3I Infotech Ltd. Mar-11 39.46
3I Infotech Ltd. Jun-11 38.8
3I Infotech Ltd. Sep-11 36.43
3I Infotech Ltd. Dec-11 22.01
3I Infotech Ltd. Mar-12 18.04
3I Infotech Ltd. Jun-12 57.82
3I Infotech Ltd. Sep-12 54.74
3I Infotech Ltd. Dec-12 55.02
3I Infotech Ltd. Mar-13 55.35
;
data want;
array t(0:12) _temporary_;
do _n_=0 by 1 until(last.Company_Name or _n_=12);
set have;
by Company_Name;
if first.company_name then call missing(of t(*));
t(_n_)=x;
if nmiss(of t(*))=0 then do;
r_mean=round(mean(of t(*)),.01);
r_std_dev=round(std(of t(*)),.01);
end;
output;
end;
run;
PROC EXPAND
Example: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=etsug&docsetTarget=etsug_... using the MOVSTD option instead of the MOVAVG option.
data have;
input Company_Name $16. Quarter $ X;
cards;
20 Microns Ltd. Mar-10 0.29
20 Microns Ltd. Jun-10 0.29
20 Microns Ltd. Sep-10 0.29
20 Microns Ltd. Dec-10 0.42
20 Microns Ltd. Mar-11 0.29
20 Microns Ltd. Jun-11 0.29
20 Microns Ltd. Sep-11 0.29
20 Microns Ltd. Dec-11 0
20 Microns Ltd. Mar-12 0
20 Microns Ltd. Jun-12 0.77
20 Microns Ltd. Sep-12 0.73
20 Microns Ltd. Dec-12 0
20 Microns Ltd. Mar-13 3.58
20 Microns Ltd. Jun-13 1.84
20 Microns Ltd. Sep-13 4.88
20 Microns Ltd. Dec-13 0
20 Microns Ltd. Mar-14 0
20 Microns Ltd. Jun-14 0
20 Microns Ltd. Sep-14 0
20 Microns Ltd. Dec-14 0
20 Microns Ltd. Mar-15 0.03
20 Microns Ltd. Jun-15 0
20 Microns Ltd. Sep-15 0
20 Microns Ltd. Dec-15 0
20 Microns Ltd. Mar-16 0
20 Microns Ltd. Jun-16 0
20 Microns Ltd. Sep-16 0
20 Microns Ltd. Dec-16 0.11
20 Microns Ltd. Mar-17 0.03
20 Microns Ltd. Jun-17 1.32
20 Microns Ltd. Sep-17 0.08
20 Microns Ltd. Dec-17 0.1
20 Microns Ltd. Mar-18 0.37
20 Microns Ltd. Jun-18 0.09
20 Microns Ltd. Sep-18 0.33
20 Microns Ltd. Dec-18 0.26
20 Microns Ltd. Mar-19 0.11
3I Infotech Ltd. Mar-10 39.02
3I Infotech Ltd. Jun-10 41.18
3I Infotech Ltd. Sep-10 37.33
3I Infotech Ltd. Dec-10 40.36
3I Infotech Ltd. Mar-11 39.46
3I Infotech Ltd. Jun-11 38.8
3I Infotech Ltd. Sep-11 36.43
3I Infotech Ltd. Dec-11 22.01
3I Infotech Ltd. Mar-12 18.04
3I Infotech Ltd. Jun-12 57.82
3I Infotech Ltd. Sep-12 54.74
3I Infotech Ltd. Dec-12 55.02
3I Infotech Ltd. Mar-13 55.35
;
data want;
array t(0:12) _temporary_;
do _n_=0 by 1 until(last.Company_Name or _n_=12);
set have;
by Company_Name;
if first.company_name then call missing(of t(*));
t(_n_)=x;
if nmiss(of t(*))=0 then do;
r_mean=round(mean(of t(*)),.01);
r_std_dev=round(std(of t(*)),.01);
end;
output;
end;
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.