topic Re: Want to find weekly start and end date for dates. in SAS Programming

Want to find weekly start and end date for dates.

ManoharNath — Thu, 21 Jul 2022 19:24:06 GMT

Hello Friends, need help on urgent basis, I want to calculate weekly start date and weekly end date in SAS.

I am trying to achive similary mentioned in linked link output: https://savvytime.com/week-number/australia/2022

below is sample data and also attached this data for your references.

we have only one input column which is Business_dt and based on Business_dt column I want to create other 3 column which i have mentioned below.

I need 3 columns in output which are

Week_yyweekv8

Start_week_dt

End_Week_dt

Business_dt	Week_yyweekv8	Start_week_dt	End_Week_dt
27-12-2021	2021-W01	27-12-2021	02-01-2022
28-12-2021	2021-W01	27-12-2021	02-01-2022
29-12-2021	2021-W01	27-12-2021	02-01-2022
30-12-2021	2021-W01	27-12-2021	02-01-2022
31-12-2021	2021-W01	27-12-2021	02-01-2022
01-01-2022	2021-W01	27-12-2021	02-01-2022
02-01-2022	2021-W01	27-12-2021	02-01-2022
03-01-2022	2022-W02	03-01-2022	09-01-2022
04-01-2022	2022-W02	03-01-2022	09-01-2022
05-01-2022	2022-W02	03-01-2022	09-01-2022
06-01-2022	2022-W02	03-01-2022	09-01-2022
07-01-2022	2022-W02	03-01-2022	09-01-2022
08-01-2022	2022-W02	03-01-2022	09-01-2022
09-01-2022	2022-W02	03-01-2022	09-01-2022
10-01-2022	2022-W03	10-01-2022	16-01-2022
11-01-2022	2022-W03	10-01-2022	16-01-2022
12-01-2022	2022-W03	10-01-2022	16-01-2022
13-01-2022	2022-W03	10-01-2022	16-01-2022
14-01-2022	2022-W03	10-01-2022	16-01-2022
15-01-2022	2022-W03	10-01-2022	16-01-2022
16-01-2022	2022-W03	10-01-2022	16-01-2022
17-01-2022	2022-W04	10-01-2022	16-01-2022
18-01-2022	2022-W04	10-01-2022	16-01-2022

Re: Want to find weekly start and end date for dates.

Reeza — Thu, 21 Jul 2022 20:10:56 GMT

Fortunately SAS handles dates pretty well.

data want;
set have;

start_week_dt = intnx('week', business_dt, 0, 'b');
end_week_dt = intnx('week', business_dt, 0, 'e');


*format instead of character variable - recommended approach;
weeky_yyweekv8 = business_dt;

week_yyweekv8_v2 = catt(year(business_dt), '-W', put(week(business_dt, 'u'), z2.));


format start_week_dt end_week_dt ddmmyyd10. weeky_yyweekv8  yyweeku8.;
run;

There are several different ways to calculate the weeks, look at the definitions here and decide which of u, v or w are appropriate for your use case.

https://documentation.sas.com/doc/en/vdmmlcdc/8.1/lefunctionsref/n1ka2ulrvrjlasn0z7beco2yrgas.htm

INTNX()

https://documentation.sas.com/doc/en/vdmmlcdc/8.1/lefunctionsref/p10v3sa3i4kfxfn1sovhi5xzxh8n.htm

EDIT: missed a semicolon, formatted text date to match date more closely.

Re: Want to find weekly start and end date for dates.

ballardw — Thu, 21 Jul 2022 20:00:32 GMT

Why do dates of 27-12-2021 have W01??? End of year I expect 52 or possibly 53.

My start would be:

data have;
   input Business_dt :ddmmyy10.;
   format Business_dt ddmmyy10.;
datalines;
27-12-2021 	
28-12-2021 	
29-12-2021 	
30-12-2021 	
31-12-2021 	
01-01-2022
09-01-2022
;

proc format;
picture myweek (default=8)
low-high='%Y-W%0V' (datatype=date)
;
run;

data want;
   set have;
   week=business_dt;
   format week myweek.;
   start_week = intnx('weekv',business_dt,0,'B');
   end_week = intnx('weekv',business_dt,0,'E');
   format start_week end_week ddmmyy10.;
run;

Assumes your Business_dt variable is actually a SAS date value. If not use Input(business_dt,ddmmyy10.) to create such a date value.

The proc format code creates a custom format to display that week value as shown. SAS provides a number of Week formats: WeekV , WeekU and WeekW that apply different rules but when using a width of 8 the appearance would be 2-digit year and include month and week. The Picture format allows directives to select and display date, time or datetime elements. In this the %Y is 4 digit year and the %0V is the week number with leading 0. Other characters are inserted verbatim. The main issue with this approach is making sure that the custom format is available as needed.

I prefer the format approach as when using a date value instead of character a graph axis behaves much nicer than a character version. The groups created by the formatted value are usable in most reporting, analysis and graphing procedures.

The Intnx function increments dates by providing an interval, starting date value, number of intervals and option Begin, End or Same position as the date value inside the interval. So 0 means "same week as" and 'B' is beginning of week.

There are several rules for assigning weeks depending on when to set boundaries and how to count the end/start of year. These are three WeekV, WeekU and WeekW which appears several places in the documentation. Yours appears to match the V rules.

@ManoharNath wrote:

Hello Friends, need help on urgent basis, I want to calculate weekly start date and weekly end date in SAS.

I am trying to achive similary mentioned in linked link output: https://savvytime.com/week-number/australia/2022

below is sample data and also attached this data for your references.

we have only one input column which is Business_dt and based on Business_dt column I want to create other 3 column which i have mentioned below.

I need 3 columns in output which are

Week_yyweekv8 Start_week_dt
End_Week_dt

Business_dt Week_yyweekv8 Start_week_dt End_Week_dt

27-12-2021 2021-W01 27-12-2021 02-01-2022

28-12-2021 2021-W01 27-12-2021 02-01-2022

29-12-2021 2021-W01 27-12-2021 02-01-2022

30-12-2021 2021-W01 27-12-2021 02-01-2022

31-12-2021 2021-W01 27-12-2021 02-01-2022

01-01-2022 2021-W01 27-12-2021 02-01-2022

02-01-2022 2021-W01 27-12-2021 02-01-2022

03-01-2022 2022-W02 03-01-2022 09-01-2022

04-01-2022 2022-W02 03-01-2022 09-01-2022

05-01-2022 2022-W02 03-01-2022 09-01-2022

06-01-2022 2022-W02 03-01-2022 09-01-2022

07-01-2022 2022-W02 03-01-2022 09-01-2022

08-01-2022 2022-W02 03-01-2022 09-01-2022

09-01-2022 2022-W02 03-01-2022 09-01-2022

10-01-2022 2022-W03 10-01-2022 16-01-2022

11-01-2022 2022-W03 10-01-2022 16-01-2022

12-01-2022 2022-W03 10-01-2022 16-01-2022

13-01-2022 2022-W03 10-01-2022 16-01-2022

14-01-2022 2022-W03 10-01-2022 16-01-2022

15-01-2022 2022-W03 10-01-2022 16-01-2022

16-01-2022 2022-W03 10-01-2022 16-01-2022

17-01-2022 2022-W04 10-01-2022 16-01-2022

18-01-2022 2022-W04 10-01-2022 16-01-2022

Some explanation:

Re: Want to find weekly start and end date for dates.

Tom — Thu, 21 Jul 2022 20:21:27 GMT

Your example seems to have some mistakes on the first week and last week.

Just use the WEEK.2 interval with INTNX(). Calculate the WEEK number from the END date of the week.

data have;
  do business_dt='27DEC2021'd to '18JAN2022'd;
    output;
  end;
  format Business_dt yymmdd10.;
run;

data test;
  set have;
  start = intnx('week.2',business_dt,0,'b');
  end = intnx('week.2',business_dt,0,'e');
  week = week(end);
  week_string=cats(year(end),'-W',put(week,z2.));
  format Start End yymmdd10.;
run;

Results:

        business_                                         week_
Obs            dt         start           end    week     string

  1    2021-12-27    2021-12-27    2022-01-02      1     2022-W01
  2    2021-12-28    2021-12-27    2022-01-02      1     2022-W01
  3    2021-12-29    2021-12-27    2022-01-02      1     2022-W01
  4    2021-12-30    2021-12-27    2022-01-02      1     2022-W01
  5    2021-12-31    2021-12-27    2022-01-02      1     2022-W01
  6    2022-01-01    2021-12-27    2022-01-02      1     2022-W01
  7    2022-01-02    2021-12-27    2022-01-02      1     2022-W01
  8    2022-01-03    2022-01-03    2022-01-09      2     2022-W02
  9    2022-01-04    2022-01-03    2022-01-09      2     2022-W02
 10    2022-01-05    2022-01-03    2022-01-09      2     2022-W02
 11    2022-01-06    2022-01-03    2022-01-09      2     2022-W02
 12    2022-01-07    2022-01-03    2022-01-09      2     2022-W02
 13    2022-01-08    2022-01-03    2022-01-09      2     2022-W02
 14    2022-01-09    2022-01-03    2022-01-09      2     2022-W02
 15    2022-01-10    2022-01-10    2022-01-16      3     2022-W03
 16    2022-01-11    2022-01-10    2022-01-16      3     2022-W03
 17    2022-01-12    2022-01-10    2022-01-16      3     2022-W03
 18    2022-01-13    2022-01-10    2022-01-16      3     2022-W03
 19    2022-01-14    2022-01-10    2022-01-16      3     2022-W03
 20    2022-01-15    2022-01-10    2022-01-16      3     2022-W03
 21    2022-01-16    2022-01-10    2022-01-16      3     2022-W03
 22    2022-01-17    2022-01-17    2022-01-23      4     2022-W04
 23    2022-01-18    2022-01-17    2022-01-23      4     2022-W04

Re: Want to find weekly start and end date for dates.

ManoharNath — Sat, 23 Jul 2022 13:06:11 GMT

Thank you for effecient solution and for quick and prompt response.

Re: Want to find weekly start and end date for dates.

ManoharNath — Sat, 23 Jul 2022 13:04:31 GMT

Thank you for quick and prompt response.