Hi to all, i hope you will be ok.
i need to calculate the average of last 7 days of my variable, like the next table.
Date | total | AVG |
19-04-2022 | 14 | 16,0 |
18-04-2022 | 7 | 24,0 |
17-04-2022 | 21 | 30,0 |
16-04-2022 | 14 | 37,0 |
15-04-2022 | 28 | 44,0 |
14-04-2022 | 14 | 52,0 |
13-04-2022 | 14 | 51,0 |
12-04-2022 | 70 | 51,0 |
11-04-2022 | 49 | 45,0 |
10-04-2022 | 70 | 43,0 |
09-04-2022 | 63 | 40,0 |
08-04-2022 | 84 | 32,0 |
07-04-2022 | 7 | 22,0 |
06-04-2022 | 14 | 24,0 |
05-04-2022 | 28 | 25,0 |
04-04-2022 | 35 | 25,0 |
03-04-2022 | 49 | 25,0 |
02-04-2022 | 7 | 24,0 |
01-04-2022 | 14 | 31,0 |
31-03-2022 | 21 | 37,0 |
30-03-2022 | 21 | 43,0 |
29-03-2022 | 28 | 50,3 |
28-03-2022 | 35 | |
27-03-2022 | 42 | |
26-03-2022 | 56 | |
25-03-2022 | 56 | |
24-03-2022 | 63 | |
23-03-2022 | 72 |
Thanks.
Note the data step that supplies example data that we can run. That really helps actually test code to see if it works.
Old school: sort and use Lag function then resort.
data have; input Date :ddmmyy10. total ; format date ddmmyy10.; datalines; 19-04-2022 14 18-04-2022 7 17-04-2022 21 16-04-2022 14 15-04-2022 28 14-04-2022 14 13-04-2022 14 12-04-2022 70 11-04-2022 49 10-04-2022 70 09-04-2022 63 08-04-2022 84 07-04-2022 7 06-04-2022 14 05-04-2022 28 04-04-2022 35 03-04-2022 49 02-04-2022 7 01-04-2022 14 31-03-2022 21 30-03-2022 21 29-03-2022 28 28-03-2022 35 27-03-2022 42 26-03-2022 56 25-03-2022 56 24-03-2022 63 23-03-2022 72 ; proc sort data=have; by date; run; data want; set have; L7=lag7(total); L6=lag6(total); L5=lag5(total); L4=lag4(total); L3=lag3(total); L2=lag2(total); L1=lag1(total); if not missing(L7) then Avg = mean(of L1-L7); drop L1-L7; run; proc sort data=want; by descending date; run;
IF you actually have more variables, such as ID that needs to be processed by the id values then any data step approach needs serious adjustments to handle the ID boundaries.
Your data are sorted in descending date order, so you need a way to look ahead up to 6 observations ahead. A self-merge with the "firstobs=" option can help:
data want (drop=_:);
merge have
have(firstobs=2 keep=total rename=(total=_total2))
have(firstobs=3 keep=total rename=(total=_total3))
have(firstobs=4 keep=total rename=(total=_total4))
have(firstobs=5 keep=total rename=(total=_total5))
have(firstobs=6 keep=total rename=(total=_total6))
have(firstobs=7 keep=total rename=(total=_total7));
if n(total,of _total:)=7 then avg=mean(total,of _total:);
run;
If you have PROC EXPAND, you can compute a moving average of N days
Example of a 3 day moving average: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/etsug/etsug_expand_examples04.htm
Note the data step that supplies example data that we can run. That really helps actually test code to see if it works.
Old school: sort and use Lag function then resort.
data have; input Date :ddmmyy10. total ; format date ddmmyy10.; datalines; 19-04-2022 14 18-04-2022 7 17-04-2022 21 16-04-2022 14 15-04-2022 28 14-04-2022 14 13-04-2022 14 12-04-2022 70 11-04-2022 49 10-04-2022 70 09-04-2022 63 08-04-2022 84 07-04-2022 7 06-04-2022 14 05-04-2022 28 04-04-2022 35 03-04-2022 49 02-04-2022 7 01-04-2022 14 31-03-2022 21 30-03-2022 21 29-03-2022 28 28-03-2022 35 27-03-2022 42 26-03-2022 56 25-03-2022 56 24-03-2022 63 23-03-2022 72 ; proc sort data=have; by date; run; data want; set have; L7=lag7(total); L6=lag6(total); L5=lag5(total); L4=lag4(total); L3=lag3(total); L2=lag2(total); L1=lag1(total); if not missing(L7) then Avg = mean(of L1-L7); drop L1-L7; run; proc sort data=want; by descending date; run;
IF you actually have more variables, such as ID that needs to be processed by the id values then any data step approach needs serious adjustments to handle the ID boundaries.
If your data has gap in DATE .
data have;
input Date :ddmmyy10. total ;
format date ddmmyy10.;
datalines;
19-04-2022 14
18-04-2022 7
17-04-2022 21
16-04-2022 14
15-04-2022 28
14-04-2022 14
13-04-2022 14
12-04-2022 70
11-04-2022 49
10-04-2022 70
09-04-2022 63
08-04-2022 84
07-04-2022 7
06-04-2022 14
05-04-2022 28
04-04-2022 35
03-04-2022 49
02-04-2022 7
01-04-2022 14
31-03-2022 21
30-03-2022 21
29-03-2022 28
28-03-2022 35
27-03-2022 42
26-03-2022 56
25-03-2022 56
24-03-2022 63
23-03-2022 72
;
proc sql;
create table want2 as
select *,case when
(select count(*) from have where date between a.date-7 and a.date-1)=7 then
(select mean(total) from have where date between a.date-7 and a.date-1)
else . end as want
from have as a;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.