30 days Moving standard deviation

[SwapnaKumari]

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	Std. 28.11.
0 365
29.11.	365
30.11.	378
1.12.	371
2.12.	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)     

[SwapnaKumari]

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

[Ksharp]

Code not tested.

data want;

merge Moving_Stdev  Moving_Stdev (firstobs=2 rename=(Std=_Std));

if Std=0 then Std=_Std;

run;

Xia Keshan

[SwapnaKumari]

Hi Xia,

Perfect, It worked, Thank you...

Regards,

Swapna

[SwapnaKumari]

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

[Ksharp]

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

[SwapnaKumari]

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

[Ksharp]

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;

[SwapnaKumari]

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

[Ksharp]

Opss.

if _n_ eq 3 then Std=lag_Std;

-->

if n eq 3 then Std=lag_Std;

[SwapnaKumari]

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