topic Re: Break a Date Range Into Multiple Lines in SAS Programming

Break a Date Range Into Multiple Lines

A_SAS_Man — Fri, 27 Mar 2020 14:48:25 GMT

I am asking a question related to a previous question I asked here: https://communities.sas.com/t5/SAS-Programming/Convert-Monthly-Data-set-to-Weekly-and-Check-for-Active-Status/m-p/635155#M188561

I am trying to make the question clearer and easier to answer. Let me first start with the example data set.

data have;
 input person $1. start_date:mmddyy10. end_date:mmddyy10.;
 format start_date date9. end_date date9.;
 datalines;
1 01/13/2019 01/19/2019
1 02/02/2019 02/28/2019
1 03/01/2019 03/28/2019
2 02/01/2019 02/28/2019
 ;
 run;

What I would like to do is break out every line into every week that date range touches. See below.

 data want;
 input person $1. start_date:mmddyy10. end_date:mmddyy10. week_start:mmddyy10. week_number 2.0;
 format start_date date9. end_date date9.;
 datalines;
1 01/13/2019 01/19/2019 01/13/2019 2
1 02/02/2019 02/28/2019 01/27/2019 4
1 02/02/2019 02/28/2019 02/03/2019 5
1 02/02/2019 02/28/2019 02/10/2019 6
1 02/02/2019 02/28/2019 02/17/2019 7
1 02/02/2019 02/28/2019 02/24/2019 8
1 03/01/2019 03/06/2019 02/24/2019 8
1 03/01/2019 03/06/2019 03/03/2019 9
2 02/02/2019 02/28/2019 01/27/2019 4
2 02/02/2019 02/28/2019 02/03/2019 5
2 02/02/2019 02/28/2019 02/10/2019 6
2 02/02/2019 02/28/2019 02/17/2019 7
2 02/02/2019 02/28/2019 02/24/2019 8
 ;
 run;

The date ranges should be consistent across people, i.e., the same start dates should have the same week numbers. The week_start field should be consistent with what you would get if you put the a date from that week into the week formula. See below for how I derived week_number

proc sql;
create table week_number_check as
	select *,
		   week(week_start) as Week_Number_Check
	from want
;
quit;

The week start dates should be consistent with how intx would calculate a the first day of the week for a given date, with the week starting on Sunday. Please let me know if this clarification.

As a summary, it seems like some looping logic is needed with the following steps.

1. Check the start_date, derive the first day of the week for that date

2. Check the end_date, derive the first day of the week for that date

3. Calculate how many weeks are included in the start_date end_date range (inclusive) and break out the single data line into that many lines, i.e, one for each week.

4. Use week() to calculate week number for each element.

Please let me know if this needs clarification.

Re: Break a Date Range Into Multiple Lines

novinosrin — Fri, 27 Mar 2020 15:43:13 GMT

Hi @A_SAS_Man 🙂 It seems you didn't account for

1 03/01/2019 03/28/2019

in your WANT for Person1 output?

Though this seems much too easy



data have;
 input person $1. start_date:mmddyy10. end_date:mmddyy10.;
 format start_date date9. end_date date9.;
 datalines;
1 01/13/2019 01/19/2019
1 02/02/2019 02/28/2019
1 03/01/2019 03/28/2019
2 02/01/2019 02/28/2019
 ;
 run;


data want;
 do until(last.person);
  set have;
  by person;
  week_start=intnx('week',start_date,0,'b');
  do while(week_start<end_date);
   week_number=week(week_start);
   output;
   week_start=intnx('week',week_start,1);
  end;
 end;
 format week_start date9.;
run;

Re: Break a Date Range Into Multiple Lines

A_SAS_Man — Fri, 27 Mar 2020 15:55:11 GMT

Hey @novinosrin :). You are correct, I missed that. This looks to be doing exactly what I want it to. Thank you so much. I'm just going to break out the active calculation I was referring to in the previous thread into a different step in my process, this was the part I was struggling with. Appreciate you sticking with me despite some of my example problem issues.

Re: Break a Date Range Into Multiple Lines

novinosrin — Fri, 27 Mar 2020 16:20:03 GMT

@A_SAS_Man You know what. It seemingly appears that the hardest part of the solution is actually getting the requirement or understanding the problem statement or communicating the problem statement effectively. The challenge is the communication. Now I really have begun to understand the importance of soft skills especially communication skills.

Technical skills can be acquired through perseverance. The rest that mentioned above is very difficult as there is no clear path/sequence to achieve that. 🙂