topic Re: 30 days Moving standard deviation in SAS Procedures

30 days Moving standard deviation

SwapnaKumari — Thu, 21 May 2015 06:48:33 GMT

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

Re: 30 days Moving standard deviation

SwapnaKumari — Thu, 21 May 2015 07:32:48 GMT

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

Re: 30 days Moving standard deviation

Ksharp — Fri, 22 May 2015 12:12:13 GMT

Code not tested.

data want;

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

if Std=0 then Std=_Std;

run;

Xia Keshan

Re: 30 days Moving standard deviation

SwapnaKumari — Fri, 22 May 2015 12:30:24 GMT

Hi Xia,

Perfect, It worked, Thank you...

Regards,

Swapna

Re: 30 days Moving standard deviation

SwapnaKumari — Thu, 28 May 2015 10:28:02 GMT

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

Re: 30 days Moving standard deviation

Ksharp — Thu, 28 May 2015 12:10:42 GMT

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

Re: 30 days Moving standard deviation

SwapnaKumari — Thu, 28 May 2015 12:50:11 GMT

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

Re: 30 days Moving standard deviation

Ksharp — Thu, 28 May 2015 13:04:28 GMT

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;

Re: 30 days Moving standard deviation

SwapnaKumari — Thu, 28 May 2015 16:56:05 GMT

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

Re: 30 days Moving standard deviation

Ksharp — Fri, 29 May 2015 12:12:22 GMT

Opss.

if _n_ eq 3 then Std=lag_Std;

-->

if n eq 3 then Std=lag_Std;

Re: 30 days Moving standard deviation

SwapnaKumari — Fri, 29 May 2015 12:21:05 GMT

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