I have a dataset (example) which looks like this:
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
;
Im using below code to calculate the mean and the stdev based on the last 4 quarters, this works perfect.
%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;
proc summary data=extract;
by type phase;
var counter;
output out=want (drop=_type_ _freq_) mean()=mean std()=std;
run;
I want to keep those mean and std and kind of merge the previous two datasets(want & extract). I would like to have a new dataset which looks like this:
type
quarter
phase
counter
percent
mean
std
AA-C
202002
1
29
6
22,75
6,448514041
AA-C
202003
1
20
6
22,75
6,448514041
AA-C
202004
1
15
6
22,75
6,448514041
AA-C
202101
1
27
6
22,75
6,448514041
AA-C
202002
2
38
4
39.5
3,8729833462
AA-C
202003
2
39
4
39.5
3,8729833462
AA-C
202004
2
36
4
39.5
3,8729833462
AA-C
202101
2
45
4
39.5
3,8729833462
AA-C
202002
3
89
4
96.75
6,6520673478
AA-C
202003
3
105
4
96.75
6,6520673478
AA-C
202004
3
95
4
96.75
6,6520673478
AA-C
202101
3
98
4
96.75
6,6520673478
BB-B
202002
1
10
11
10
0
BB-B
202003
1
10
1
10
0
BB-B
202004
1
10
1
10
0
BB-B
202101
1
10
1
10
0
BB-B
202002
2
20
4
20
0
BB-B
202003
2
20
3
20
0
BB-B
202004
2
20
3
20
0
BB-B
202101
2
20
3
20
0
BB-B
202002
3
5
4
6.5
1
BB-B
202003
3
7
3
6.5
1
BB-B
202004
3
7
3
6.5
1
BB-B
202101
3
7
3
6.5
1
... View more