topic Re: Consecutive week number based on date in SAS Programming

Consecutive week number based on date

chris2377 — Thu, 19 Mar 2020 12:41:39 GMT

Hi,

I have a long series of data, recorded daily from Monday to Friday. I'd like to add a variable that numbers consecutive weeks - it will equal 1 for the first 5 obs, 2 for observations 6-10 etc. (like in the example below). How to do it?

data have;
		input date :yymmdd10.;
		format date yymmdd10.;
		datalines;
		2020-01-06
		2020-01-07
		2020-01-08
		2020-01-09
		2020-01-10
		2020-01-13
		2020-01-14
		2020-01-15
		2020-01-16
		2020-01-17
		2020-01-20
		2020-01-21
		2020-01-22
		2020-01-23
		2020-01-24
		;
	run;

	data want;
		input date :yymmdd10. week_no;
		format date yymmdd10.;
		datalines;
		2020-01-06 1
		2020-01-07 1
		2020-01-08 1
		2020-01-09 1
		2020-01-10 1
		2020-01-13 2
		2020-01-14 2
		2020-01-15 2
		2020-01-16 2
		2020-01-17 2
		2020-01-20 3
		2020-01-21 3
		2020-01-22 3
		2020-01-23 3
		2020-01-24 3
		;
	run;

Best

Re: Consecutive week number based on date

data_null__ — Thu, 19 Mar 2020 12:46:36 GMT

w = week(date);

Re: Consecutive week number based on date

PeterClemmensen — Thu, 19 Mar 2020 12:47:40 GMT

How about

data have;
input date :yymmdd10.;
format date yymmdd10.;
datalines;
2020-01-06
2020-01-07
2020-01-08
2020-01-09
2020-01-10
2020-01-13
2020-01-14
2020-01-15
2020-01-16
2020-01-17
2020-01-20
2020-01-21
2020-01-22
2020-01-23
2020-01-24
;

data want;
   set have;
   if mod(_N_, 5) = 1 then weekno + 1;
run;

Re: Consecutive week number based on date

chris2377 — Thu, 19 Mar 2020 12:51:28 GMT

@PeterClemmensen
Perfect, thanks!

Re: Consecutive week number based on date

PeterClemmensen — Thu, 19 Mar 2020 12:53:20 GMT

Anytime. My code is a direct solution to your specific problem. I must address, that you should take advatage of the week() function if applicable.

Re: Consecutive week number based on date

chris2377 — Thu, 19 Mar 2020 12:53:20 GMT

@data_null__
I've tried that first, but it doesn't work properly when there's a year change

Re: Consecutive week number based on date

PaigeMiller — Thu, 19 Mar 2020 12:53:38 GMT

I guess I have concerns about both of the above solutions, as it could be possible that they fail in certain situations. In particular, relying on _N_ requires that there always be five days listed for each week; if a day isn't listed because its a holiday, then this method fails. The WEEK function works fine within a year, but if the data set is 53 or more weeks long, then (for example), Jan 3 2020 and Jan 3 2021 would be the same week.

I would use the INTCK function to determine the number of weeks after the first date.