avg last 7 dates

[Andres_Fuentes1]

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.

[mkeintz]

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;

[PaigeMiller]

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

[ballardw · Accepted solution]

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.

[Ksharp]

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;