🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## How to calculate variables inside a group?

For every PHASE in every TYPE I'm trying to calculate the AVG and STD based on the last 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)``````

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How to calculate variables inside a group?

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;``````
7 REPLIES 7
Diamond | Level 26

## Re: How to calculate variables inside a group?

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/7359...

--
Paige Miller
Obsidian | Level 7

## Re: How to calculate variables inside a group?

@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.
Diamond | Level 26

## Re: How to calculate variables inside a group?

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.

--
Paige Miller
Obsidian | Level 7

## Re: How to calculate variables inside a group?

@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:

Diamond | Level 26

## Re: How to calculate variables inside a group?

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;``````
--
Paige Miller
Obsidian | Level 7

## Re: How to calculate variables inside a group?

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

Super User

## Re: How to calculate variables inside a group?

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;``````
Discussion stats
• 7 replies
• 458 views
• 2 likes
• 3 in conversation