topic Re: year to date Standard Deviation in SAS Programming

year to date Standard Deviation

Amarpal — Thu, 19 Nov 2015 20:06:38 GMT

I have a dataset with Dates, a variable that defines what the value measures, and the value. I want to get the year-to-date standard deviation for each date. So i have something like this, and want to create this Std column...

Date Measure value std

jan01 Batch Payment 93 .

jan08 Batch Payment 128 24.64

jan15 Batch Payment 126 19.58

jan01 Cost per Claim 311

jan08 Cost per Claim 385 18.38

jan15 Cost per Claim 281 16.29

jan01 Pended Claims 22372

jan08 Pended Claims 18389 2816.41

jan15 Pended Claims 15869 3278.81

I tried using this...

data testing;
set test2;

retain count;
by measure;

if first.measure then count=1;
else count=1+count;

array std_weeks[46] _temporary_;

std_weeks[count]=value;

if first.measure then std = .;
else std = std(of std_weeks[*]);

run;

Right now I have 46 weeks of data, I plan to read the max of count into a variable and use that variable in place of the 46. What I have there works for the first group of measure, but the next measure's std includes values from the previous measure (since the array is set to 46). Can I make this work with arrays? Is there another way to achieve the desired result?

Thanks,

-Amarpal

Re: year to date Standard Deviation

Reeza — Thu, 19 Nov 2015 20:11:09 GMT

You need to reset your array when the measure changes.

See code here that works for other moving metrics:

https://communities.sas.com/message/244232

Re: year to date Standard Deviation

Astounding — Thu, 19 Nov 2015 20:34:39 GMT

Here's another approach, just in case this appeals to you more.

If you wanted the mean instead of the standard deviation, you could just keep running SUM and running COUNT. Then on each observation divide to get the mean. Of course, you would have to re-set the running SUM and running COUNT each time you hit a new MEASURE ... but you're already doing that for the COUNT.

Similarly, standard deviation can be expressed as a function of the running SUM, running COUNT, and running SUM OF SQUARED VALUES. By applying that sort of a formula, you would never have to worry about how many array elements you need.

Good luck.

Re: year to date Standard Deviation

PGStats — Thu, 19 Nov 2015 21:32:26 GMT

The sort of formula @Astounding is referring to would work like this:

data testing;
set test2; by measure;
if first.measure then do; n=0; sx=0; sx2=0; end;
if not missing(value) then do;
    sx + value;
    sx2 + value*value;
    n + 1;
    if n>1 then std = sqrt((sx2-sx*sx/n)/(n-1));
    end;
drop n sx sx2;
run;