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

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-Acti...

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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;

A_SAS_Man
Pyrite | Level 9

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.

novinosrin
Tourmaline | Level 20

@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. 🙂

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1044 views
  • 1 like
  • 2 in conversation