topic Re: avg last 7 dates in SAS Enterprise Guide

avg last 7 dates

Andres_Fuentes1 — Wed, 20 Apr 2022 16:21:59 GMT

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.

Re: avg last 7 dates

mkeintz — Wed, 20 Apr 2022 16:59:17 GMT

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;

Re: avg last 7 dates

PaigeMiller — Wed, 20 Apr 2022 17:02:11 GMT

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

Re: avg last 7 dates

ballardw — Wed, 20 Apr 2022 17:49:43 GMT

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.

Re: avg last 7 dates

Ksharp — Thu, 21 Apr 2022 13:10:41 GMT

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;