BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mitrakos
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION
3 REPLIES 3
Reeza
Super User
  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
Rhodochrosite | Level 12

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;

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 974 views
  • 2 likes
  • 4 in conversation