topic Re: How to calculate std with group by , on a growing data in SAS Programming

How to calculate std with group by , on a growing data

J111 — Mon, 23 Sep 2024 06:20:19 GMT

Hello,

My understanding, a new topic should be opened for this by group question:-

Next, if we wanted to add another field such as group by and

calculate the std for each value of gr in the data below:-

what would be the best solution ? Thanks a lot.

data available ;
input gr dt y ;
cards ;
1 1 80
1 2 20
1 3 40
1 4 60
2 1 80
2 2 20
2 3 40
2 4 50
;
run ;

data wanted ;
input gr dt y std ;
cards ;
1 1 80
1 2 20 42.426406871
1 3 40 30.550504633
1 4 60 25.819888975
2 1 80
2 2 20 42.426406871
2 3 40 30.550504633
2 4 50 25
;
run ;

Re: How to calculate std with group by , on a growing data

Ksharp — Mon, 23 Sep 2024 07:59:55 GMT

Did you try my code ?

data available ;
input gr dt y ;
cards ;
1 1 80
1 2 20
1 3 40
1 4 60
2 1 80
2 2 20
2 3 40
2 4 50
;
data want;
 set available;
 by gr;
 array x{999999} _temporary_;
 if first.gr then do;n=0;call missing(of x{*});end;
 n+1;
 x{n}=y;
 std=std(of x{*});
 drop n;
run;

Re: How to calculate std with group by , on a growing data

FreelanceReinh — Mon, 23 Sep 2024 08:10:07 GMT

Hello @J111,

My solution from the previous thread can be generalized to BY groups as well:

data want(drop=_:);
_s=0; _v=0;
do _n=1 by 1 until(last.gr);
  set available;
  by gr;
  _s+y; /* cumulative sum */
  _m=_s/_n; /* cumulative mean */
  _d=dif(_m); /* mean change */
  _q=(y-_m)**2; /* new term in sum of squares */
  if _n>1 then do;
    std=sqrt(_v+_d**2+_q/(_n-1)); /* cumulative standard deviation */
    _v=((_n-1)*(_v+_d**2)+_q)/_n; /* cumulative population variance */
  end;
  output;
end;
run;

Re: How to calculate std with group by , on a growing data

J111 — Mon, 23 Sep 2024 08:36:41 GMT

Thanks a lot it works

super

Re: How to calculate std with group by , on a growing data

J111 — Mon, 23 Sep 2024 08:38:56 GMT

Thanks it works !!

Great

Re: How to calculate std with group by , on a growing data

J111 — Mon, 23 Sep 2024 08:46:44 GMT

It would be nice to see the solution using the logic below (from P.Miller),

but just for the by group..

data _NULL_;
	if 0 then set available nobs=n;
	call symputx('nrows',n);
	stop;
run;


data intermediate;
    set available;
    do group=_n_ to &nrows;
        thisy=y;
        output;
    end;
run;
        
proc summary data=intermediate nway;
    class group;
    var thisy;
    output out=want stddev=;
run;

using

Re: How to calculate std with group by , on a growing data

PaigeMiller — Mon, 23 Sep 2024 11:06:39 GMT

data available ;
input gr dt y ;
cards ;
1 1 80
1 2 20
1 3 40
1 4 60
2 1 80
2 2 20
2 3 40
2 4 50
;

proc freq data=available;
    tables gr/noprint out=_a_;
run;

data intermediate;
    merge available _a_;
    by gr;
    if first.gr then seq=0;
    seq+1;
    do group=seq to count;
        thisy=y;
        output;
    end;
run;

proc summary data=intermediate nway;
    class gr group;
    var thisy;
    output out=want stddev=;
run;

Re: How to calculate std with group by , on a growing data

J111 — Mon, 23 Sep 2024 11:37:27 GMT

Thanks a lot -

May I point out that this solution has much better performance compared to the array post