🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 11-27-2020 11:57 AM
(981 views)
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
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
3 REPLIES 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
--
Paige Miller
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Can you assume you have data for every quarter consecutively and are not missing months?
I'll leave the part of adding logic to only calculate once you have 12 quarters up to you - NMISS() function + IF.
data want;
array p{0:11} _temporary_;
set have;
by companyName Quarter;
if first.companyName then call missing(of p{*});
p{mod(_n_,12)} = price;
rolling_mean= mean(of p{*});
rolling_std= std(of p{*});
run;
https://gist.github.com/statgeek/27e23c015eae7953eff2
I'll leave the part of adding logic to only calculate once you have 12 quarters up to you - NMISS() function + IF.
data want;
array p{0:11} _temporary_;
set have;
by companyName Quarter;
if first.companyName then call missing(of p{*});
p{mod(_n_,12)} = price;
rolling_mean= mean(of p{*});
rolling_std= std(of p{*});
run;
https://gist.github.com/statgeek/27e23c015eae7953eff2
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;