Week number starting from 1 for any month

[msatl]

Hello Community,

I'm fairly new to SAS and need your help with a query. I have a date column in my dataset, which is of 'DATE' type variable and I would like to add a column to the dataset that gives me the week number corresponding to the dates. I can use the week() function but there's slightly a different requirement in my case as I want the week number to start from 1 irrespective of the month that the date starts from. Please note that my date always starts with Monday and may have multiple occurrences of same date. Also, I want the week to start from Monday and end on Sunday, so the week number should be corresponding to it. Below is a sample dataset. 

date_var	week_no
2023-06-12	1
2023-06-12	1
2023-06-13	1
2023-06-14	1
2023-06-15	1
2023-06-16	1
2023-06-17	1
2023-06-18	1
2023-06-19	2
2023-06-20	2
2023-06-21	2
2023-06-22	2
2023-06-23	2
2023-06-24	2
2023-06-25	2
2023-06-26	3
2023-06-27	3
2023-06-28	3
2023-06-29	3
2023-06-30	3
2023-07-01	3
2023-07-02	3

I have tried the query below but it doesn't start the week from 1.

data final_table;

set temp_table;

week_no=week(date_var);

run;

[Reeza]

Will week ever be past 5? Does the count ever reset?

If not use the first date as your start_date and and then use math functions to get the week. If you want to adjust the first for mondays, etc use INTNX with the WEEK and interval parameters to set it up.

data final_table;
set temp_table;
retain start_date;
if _n_ = 1 then start_date = date_var;

week = floor((date_var-start_date)/7)+1;
run;

[PaigeMiller · Accepted solution]

data have;
    input date :yymmdd10.;
    format date yymmdd10.;
    cards;
2023-06-12	
2023-06-12	
2023-06-13	
2023-06-14	
2023-06-15	
2023-06-16	
2023-06-17	
2023-06-18	
2023-06-19	
2023-06-20	
2023-06-21	
2023-06-22	
2023-06-23	
2023-06-24	
2023-06-25	
2023-06-26	
2023-06-27	
2023-06-28	
2023-06-29	
2023-06-30	
2023-07-01	
2023-07-02	
;

data want;
    set have;
    retain first_day;
    if _n_=1 then first_day=date;
    week_no=1+floor((date-first_day+1)/7);
    if weekday(date)=1 then week_no=week_no-1;
run;

Explanation: from the first day of the data set (which has to be a Monday, you said), weeks can be found by finding the number of days elapsed and then dividing by 7 and rounding down to an integer. The exception is when the date is Sunday, we subtract 1 from the week number to get the corrected week number.

[Tom]

Do you just want to count how many weeks since the first date value?

That generates the same numbers as your WEEK_NO variable.

data have;
  input date_var :yymmdd. week_no ;
  format date_var yymmdd10.;
cards;
2023-06-12 1
2023-06-12 1
2023-06-13 1
2023-06-14 1
2023-06-15 1
2023-06-16 1
2023-06-17 1
2023-06-18 1
2023-06-19 2
2023-06-20 2
2023-06-21 2
2023-06-22 2
2023-06-23 2
2023-06-24 2
2023-06-25 2
2023-06-26 3
2023-06-27 3
2023-06-28 3
2023-06-29 3
2023-06-30 3
2023-07-01 3
2023-07-02 3
;

data want;
  set have;
  if _n_=1 then start_date=date_var;
  retain start_date ;
  format start_date yymmdd10.;
  want = 1 + int( (date_var-start_date)/7 );
run;

[mkeintz]

Given your problem description, I guess you want the earliest DATE in a dataset to be assigned to week 1, correct?    Then

data have;
  input date_var  yymmdd12. 	desired_week_no;
  format date_var  date. ;
datalines;
2023-06-12	1
2023-06-12	1
2023-06-13	1
2023-06-14	1
2023-06-15	1
2023-06-16	1
2023-06-17	1
2023-06-18	1
2023-06-19	2
2023-06-20	2
2023-06-21	2
2023-06-22	2
2023-06-23	2
2023-06-24	2
2023-06-25	2
2023-06-26	3
2023-06-27	3
2023-06-28	3
2023-06-29	3
2023-06-30	3
2023-07-01	3
2023-07-02	3
run;


data want (drop=_:);
  set have;
  if _n_=1 then set have (keep=date_var rename=(date_var=_original_date_var));
  week_num=1+intck('week.2',_original_date_var,date_var);
run;

The 'week.2' time interval is a week that begins on Mondays, which I understand is your definition of a week.