Finding average over a period of two weeks

[mitrakos]

Hello!

I am trying to figure out how to find the average of a value based off a range of time, specifically two weeks. Sample data is below

data test;
input ID date :yymmdd10. value;
format date yymmdd10.;
datalines;
1 2020-04-01 110
1 2020-04-02 120
1 2020-04-03 130
1 2020-04-04 110
1 2020-04-05 110
1 2020-04-06 115
1 2020-04-07 120 
1 2020-04-08 140 
1 2020-04-09 130
1 2020-04-10 110
1 2020-04-11 120
1 2020-04-12 130
1 2020-04-13 120
1 2020-04-14 130
1 2020-04-15 140
1 2020-04-16 135
1 2020-04-17 135
1 2020-04-18 130
1 2020-04-19 150
1 2020-04-20 155
1 2020-04-21 140
1 2020-04-22 145
1 2020-04-23 150
1 2020-04-24 150
1 2020-04-25 155
1 2020-04-26 145
1 2020-04-27 150
1 2020-04-28 160
1 2020-04-29 165
1 2020-04-30 165
;
run;

The dataset I am actually working with includes many more observations, from September 2019 to July 2020, but this is just a subset. I would like to find the average of the 'value' variable across a timeframe of two weeks, so Bimonthly essentially. Any idea on how to perform this? Thanks!

[Kurt_Bremser · Accepted solution]

Create a new group variable, as FLOOR((date - startdate) / 14). That will give you a running count of bi-weeks.

[Reeza]

1. Does the 'week' start on the first day in your data set? The first day in September? The first Sunday in September?
2. Can a week cross months, what happens to weeks that split the month?
3. Is it a moving week or two week intervals entirely?
4. Can you have duplicate entries for as single date? If so, how will those be handled?
5. Can you have data missing for a date? If so, how will those be handled?
6. Is it for a single ID or multiple IDs?
   
   You may want to expand your sample data to cover some of the questions above, if they apply.
   
   

Spoiler

---

@mitrakos wrote:

Hello!

I am trying to figure out how to find the average of a value based off a range of time, specifically two weeks. Sample data is below

data test;
input ID date :yymmdd10. value;
format date yymmdd10.;
datalines;
1 2020-04-01 110
1 2020-04-02 120
1 2020-04-03 130
1 2020-04-04 110
1 2020-04-05 110
1 2020-04-06 115
1 2020-04-07 120 
1 2020-04-08 140 
1 2020-04-09 130
1 2020-04-10 110
1 2020-04-11 120
1 2020-04-12 130
1 2020-04-13 120
1 2020-04-14 130
1 2020-04-15 140
1 2020-04-16 135
1 2020-04-17 135
1 2020-04-18 130
1 2020-04-19 150
1 2020-04-20 155
1 2020-04-21 140
1 2020-04-22 145
1 2020-04-23 150
1 2020-04-24 150
1 2020-04-25 155
1 2020-04-26 145
1 2020-04-27 150
1 2020-04-28 160
1 2020-04-29 165
1 2020-04-30 165
;
run;

The dataset I am actually working with includes many more observations, from September 2019 to July 2020, but this is just a subset. I would like to find the average of the 'value' variable across a timeframe of two weeks, so Bimonthly essentially. Any idea on how to perform this? Thanks!

---

[PhilC]

I'm glad you didn't say "running" average.

I love the INTNX() function.  This function delivers a way to make two week time periods.  To @Reeza 's point I believe this starts on Sunday.  You should check for yourself, but the interval parameter can be tweaked to be Monday using something like "week2.1".

data intnx;
  set test;
  date_BO2WP=intnx("week2",date,0);
  label date_BO2WP = "Beginning of two week period";
  format date: yymmdd10.;
run;

PROC MEANS DATA=WORK.INTNX
	NOPRINT
	CHARTYPE
	NWAY 
  MEAN NONOBS 	;

	VAR value;
	CLASS date_BO2WP /	ORDER=UNFORMATTED ASCENDING;
	BY ID;

OUTPUT 	OUT=WORK.MEANSummaryStats (drop=_WAY_ _TYPE_)
	
		MEAN()=

	/ AUTONAME AUTOLABEL  WAYS INHERIT
	;
RUN;