Hi All,
I am trying to calculate 30 days moving standard deviation using Proc expand, and able to get the same in the output, I have used group by variable as per the requirement, Now the issue is in the result I am getting is having 0 value in every 1st observation in the output. I know The standard deviation of the first value of a considered data series is always zero, as it is not possible to calculate it with only one value. So I have requirement that the initial standard deviation shall be equal to the one of the second date. Please find the below example for more clarity.
Example:
As of date |
|
0 365 | |
| 365 |
| 378 |
| 371 |
| 392 |
Below is the current code:-
PROC EXPAND DATA = temp1 OUT=Moving_Stdev ;
CONVERT Exposure = Moving_Stdev30 / TRANSFORMOUT=(MOVSTD 30);
by Instrument_Type Country;
RUN;
Can anyone please help me to achieve the above requirement, a sample code would really help.
Thanks in advance.
Regards,
Swapna
2)
Apologies, I think the above example is not very clear so sending the more clear one on what I exactly need.
Example:
As of date | Std. | |
28/11/2014 | 0 | Instead of 0 it should pick up the 2nd value i.e 365 |
29/11/2014 | 365 | |
30/11/2014 | 378 | |
1/12/2014 | 371 | |
2/12/2014 | 392 |
Regards,
Swapna
Code not tested.
data want;
merge Moving_Stdev Moving_Stdev (firstobs=2 rename=(Std=_Std));
if Std=0 then Std=_Std;
run;
Xia Keshan
Hi Xia,
Perfect, It worked, Thank you...
Regards,
Swapna
Hi Xia,
Now I want the 3 cell value of standard deviation also to be same as 2nd cell value of standard deviation. for example:-
As of date | Std. | |
28/11/2014 | 0 | Instead of 0 it should pick up the 2nd value i.e 365 |
29/11/2014 | 365 | |
30/11/2014 | 365 | 3rd value should also be as same as 2nd value |
1/12/2014 | 371 | |
2/12/2014 | 392 |
It would be really great if you can please share a sample code for the above as well.
Regards,
Swapna
OK. Code not tested.
I don't know if there are some group variables , Just assuming not.
data want;
merge Moving_Stdev Moving_Stdev (firstobs=2 rename=(Std=_Std));
if Std=0 then Std=_Std;
lag_Std=lag(Std);
if _n_ eq 3 then Std=lag_Std;
run;
Xia Keshan
Hi Xia,
Yes I do have the group variables please find the proc expand code which will give u insight of the group variables I have in my data set:-
PROC EXPAND DATA = temp1 OUT=Moving_Stdev ;
CONVERT Exposure = Moving_Stdev30 / TRANSFORMOUT=(MOVSTD 30);
by Instrument_Type Country;
RUN;
your code is running fine but i need the code which should change all the by group variable 3rd cell value with 2nd cell value
Regards,
Swapna
OK.
data want;
merge Moving_Stdev Moving_Stdev (firstobs=2 rename=(Std=_Std));
by Instrument_Type Country;
if first.Country then do;n=0;Std=_Std;end;
n+1;
lag_Std=lag(Std);
if _n_ eq 3 then Std=lag_Std;
run;
Hi Xia,
I tried running the code shared by you.. It is working only for 1st group n failing for remaining groups. also now the first value 0 for the remaining groups are not replacing with the second value..
something is gong wrong can you please help.
Please find the same input::-
as_of_date | Instrument_Type | Country | TIME | Moving_Stdev30 | |
28Nov2013 | Commercial loans | Australia | 0 | 0.00 | need to repalce 0 with 2nd value i.e 6.22 |
29Nov2013 | Commercial loans | Australia | 1 | 6.22 | |
02Dec2013 | Commercial loans | Australia | 2 | 9.53 | need to replace 3rd value with 2nd value i.e 6.22 |
03Dec2013 | Commercial loans | Australia | 3 | 77.73 | |
04Dec2013 | Commercial loans | Australia | 4 | 68.23 | |
05Dec2013 | Commercial loans | Australia | 5 | 80.13 | ` |
28Nov2013 | Commercial loans | Austria | 0 | 0.00 | |
29Nov2013 | Commercial loans | Austria | 1 | 30.55 | |
02Dec2013 | Commercial loans | Austria | 2 | 52.76 | |
03Dec2013 | Commercial loans | Austria | 3 | 50.97 | |
04Dec2013 | Commercial loans | Austria | 4 | 44.43 | |
05Dec2013 | Commercial loans | Austria | 5 | 42.34 |
Regards,
Swapna
Opss.
if _n_ eq 3 then Std=lag_Std;
-->
if n eq 3 then Std=lag_Std;
Hi Xia,
Thank you for your reply but your code is still not working with the groups it is only replacing the first group and remaining are still same.
anyways I found a different way to calculate the same, so sharing the code with you..
/*Replacing the 0 Values in stadev varibale with the 2nd value of Stdev*/
data temp1;
merge Moving_Stdev Moving_Stdev (firstobs=2 rename=(Stdev=_Std));
if Stdev="0" then Stdev=_Std;
drop _Std;
run;
/*Replacing the 3rd Value in every group in stadev varibale with the 2nd value of Stdev*/
data Final ;
set temp1;
l2=lag1(Stdev);
if time = 3 then Stdev = l2;
drop time l2;
run;
Thanks for your help..
Regards,
Swapna
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.