topic Re: Finding average over a period of two weeks in New SAS User

Finding average over a period of two weeks

mitrakos — Tue, 22 Dec 2020 16:50:53 GMT

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!

Re: Finding average over a period of two weeks

Kurt_Bremser — Tue, 22 Dec 2020 16:56:37 GMT

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

Re: Finding average over a period of two weeks

Reeza — Tue, 22 Dec 2020 17:01:31 GMT

Does the 'week' start on the first day in your data set? The first day in September? The first Sunday in September?
Can a week cross months, what happens to weeks that split the month?
Is it a moving week or two week intervals entirely?
Can you have duplicate entries for as single date? If so, how will those be handled?
Can you have data missing for a date? If so, how will those be handled?
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.

@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!

Re: Finding average over a period of two weeks

PhilC — Tue, 22 Dec 2020 17:09:31 GMT

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;