topic Re: How to calculate variables inside a group? in SAS Enterprise Guide

How to calculate variables inside a group?

Andalusia — Wed, 28 Apr 2021 09:49:04 GMT

For every PHASE in every TYPE I'm trying to calculate the AVG and STD based on the last 4 quarters. This is my current code:

data have;
  infile datalines truncover;
  input type $ quarter phase counter percent;
  datalines;
BB-B 202001 1 94 13
BB-B 202001 2 91 2
BB-B 202001 3 50 2
BB-B 202002 1 10 11
BB-B 202002 2 20 4
BB-B 202002 3 5 4
BB-B 202003 1 10 1
BB-B 202003 2 20 3
BB-B 202003 3 7 3
BB-B 202004 1 10 1
BB-B 202004 2 20 3
BB-B 202004 3 7 3
BB-B 202101 1 10 1
BB-B 202101 2 20 3
BB-B 202101 3 7 3
AA-C 202001 1 67 6
AA-C 202001 2 38 4
AA-C 202001 3 68 4
AA-C 202002 1 29 6
AA-C 202002 2 38 4
AA-C 202002 3 89 4
AA-C 202003 1 20 6
AA-C 202003 2 39 4
AA-C 202003 3 105 4
AA-C 202004 1 15 6
AA-C 202004 2 36 4
AA-C 202004 3 95 4
AA-C 202101 1 27 6
AA-C 202101 2 45 4
AA-C 202101 3 98 4
;
proc sql;
create table want as
select  type,phase,mean(counter) as mean,std(counter) as std
from (
select * from have group by type having quarter=max(quarter) or quarter=max(quarter)-1 or quarter=max(quarter)-2 or quarter=max(quarter)-3
)
group by type,phase;
quit;

This is my current output:

This is my desired output:

type phase AVG SDeviation BB-B 1 (10+10+10+10)/4 (Sdeviation here) BB-B 2 (20+20+20+20)/4 (Sdeviation here) BB-B 3 (7+7+7+5)/4 (Sdeviation here) AA-C 1 (27+15+20+29)/4 (Sdeviation here) AA-C 2 (45+36+39+38)/4 (Sdeviation here) AA-C 3 (98+95+105+89)/4 (Sdeviation here)

Re: How to calculate variables inside a group?

PaigeMiller — Wed, 28 Apr 2021 09:49:53 GMT

How is this question different than your earlier question, in which an answer was given to compute the results for the last X quarters?

https://communities.sas.com/t5/SAS-Enterprise-Guide/How-to-make-calculations-inside-a-group/m-p/735968

Re: How to calculate variables inside a group?

Andalusia — Wed, 28 Apr 2021 09:52:04 GMT

@PaigeMiller, that question was only about calculating the last 2 quarters. This one is about calculating the last 4 quarters. So this question also has different sample data.

Re: How to calculate variables inside a group?

PaigeMiller — Wed, 28 Apr 2021 09:56:39 GMT

In your other thread, Kurt Bremser explained how to get the results you want from the last X quarters. Just because the data is different isn't a good explanation, because it seems the goal is the same, so it should work regardless of the data.

So, again I am not sure what is different, and a more detailed explanation would help.

Re: How to calculate variables inside a group?

Andalusia — Wed, 28 Apr 2021 10:22:26 GMT

@PaigeMiller @Kurt_Bremser So I tried Kurts snippet on aboves sample data but I did not got the desired output:

Kurt's Snippet:

%let n_quarters = 4; proc sort data=have; by type phase quarter; run; data extract; counta = 0; do until (last.phase); set have; by type phase; counta + 1; end; countb = 0; do until (last.phase); set have; by type phase; countb + 1; if countb gt (counta - &n_quarters.) then output; end; drop counta countb; run;

Output:

Re: How to calculate variables inside a group?

PaigeMiller — Wed, 28 Apr 2021 10:33:21 GMT

And then all you have to do is compute means and standard deviations by TYPE and PHASE

proc summary data=extract nway;
    class type phase;
    var counter;
    output out=want mean=count_mean std=count_std;
run;

Re: How to calculate variables inside a group?

Kurt_Bremser — Wed, 28 Apr 2021 10:39:14 GMT

You now have a nice dataset with the last 4 quarters for every group, now you just need to run the statistics on it:

proc summary data=extract;
by type phase;
var counter;
output out=want (drop=_type_ _freq_) mean()=mean std()=std;
run;

Re: How to calculate variables inside a group?

Andalusia — Wed, 28 Apr 2021 11:09:31 GMT

@PaigeMiller @Kurt_Bremser Thank you both. That was indeed the last step I missed!