SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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_NameQuarterX
20 Microns Ltd.Mar-100.29
20 Microns Ltd.Jun-100.29
20 Microns Ltd.Sep-100.29
20 Microns Ltd.Dec-100.42
20 Microns Ltd.Mar-110.29
20 Microns Ltd.Jun-110.29
20 Microns Ltd.Sep-110.29
20 Microns Ltd.Dec-110
20 Microns Ltd.Mar-120
20 Microns Ltd.Jun-120.77
20 Microns Ltd.Sep-120.73
20 Microns Ltd.Dec-120
20 Microns Ltd.Mar-133.58
20 Microns Ltd.Jun-131.84
20 Microns Ltd.Sep-134.88
20 Microns Ltd.Dec-130
20 Microns Ltd.Mar-140
20 Microns Ltd.Jun-140
20 Microns Ltd.Sep-140
20 Microns Ltd.Dec-140
20 Microns Ltd.Mar-150.03
20 Microns Ltd.Jun-150
20 Microns Ltd.Sep-150
20 Microns Ltd.Dec-150
20 Microns Ltd.Mar-160
20 Microns Ltd.Jun-160
20 Microns Ltd.Sep-160
20 Microns Ltd.Dec-160.11
20 Microns Ltd.Mar-170.03
20 Microns Ltd.Jun-171.32
20 Microns Ltd.Sep-170.08
20 Microns Ltd.Dec-170.1
20 Microns Ltd.Mar-180.37
20 Microns Ltd.Jun-180.09
20 Microns Ltd.Sep-180.33
20 Microns Ltd.Dec-180.26
20 Microns Ltd.Mar-190.11
3I Infotech Ltd.Mar-1039.02
3I Infotech Ltd.Jun-1041.18
3I Infotech Ltd.Sep-1037.33
3I Infotech Ltd.Dec-1040.36
3I Infotech Ltd.Mar-1139.46
3I Infotech Ltd.Jun-1138.8
3I Infotech Ltd.Sep-1136.43
3I Infotech Ltd.Dec-1122.01
3I Infotech Ltd.Mar-1218.04
3I Infotech Ltd.Jun-1257.82
3I Infotech Ltd.Sep-1254.74
3I Infotech Ltd.Dec-1255.02
3I Infotech Ltd.Mar-1355.35

 

i want the output in the following format

Company_NameQuarterXRolling_meanRolling_Std.dev
20 Microns Ltd.Mar-100.29  
20 Microns Ltd.Jun-100.29  
20 Microns Ltd.Sep-100.29  
20 Microns Ltd.Dec-100.42  
20 Microns Ltd.Mar-110.29  
20 Microns Ltd.Jun-110.29  
20 Microns Ltd.Sep-110.29  
20 Microns Ltd.Dec-110  
20 Microns Ltd.Mar-120  
20 Microns Ltd.Jun-120.77  
20 Microns Ltd.Sep-120.73  
20 Microns Ltd.Dec-120  
20 Microns Ltd.Mar-133.580.560.94
20 Microns Ltd.Jun-131.840.681.00
20 Microns Ltd.Sep-134.881.031.52
20 Microns Ltd.Dec-1301.011.54
20 Microns Ltd.Mar-1400.971.56
20 Microns Ltd.Jun-1400.951.57
20 Microns Ltd.Sep-1400.931.58
20 Microns Ltd.Dec-1400.911.59
20 Microns Ltd.Mar-150.030.911.59
20 Microns Ltd.Jun-1500.911.59
20 Microns Ltd.Sep-1500.851.61
20 Microns Ltd.Dec-1500.791.63

 

please suggest a suitable SAS code.

thanks in advance  

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;



View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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
Reeza
Super User
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

novinosrin
Tourmaline | Level 20

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;



sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 982 views
  • 2 likes
  • 4 in conversation