Create Week Variable for Last 12 weeks, Year Over Year

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Create Week Variable for Last 12 weeks, Year Over Year

Hi there, I have recently run into a problem creeating a week variable.  What I had coded last year doesn't seem to handle changes in the year, so I am looking for a new approach.  I am trying to create a week variable that ends with the yesterday and goes back the past 12 weeks, while also looking at the same day last year and pulling the 12 weeks prior.  Here is the code I was using:

 

If year("&cywe."d) = year then WeekNo = ceil((date-"&cywe."d)/7);
else WeekNo = ceil((date-"&pywe."d)/7);

Where the macro variable just pull the prior day and the prior day last year.  So the current week would be 0 (ending yesterday), would be 0, last week would be -1 and so on.  It was working until we moved into 2016, but now it doesn't recognize that the last weeks of 2015 should preceed the first weeks of 2016.

 

Is there a better way to do this that would work all year long?  Thanks!


Accepted Solutions
Solution
‎02-10-2016 06:26 PM
Super User
Posts: 11,113

Re: Create Week Variable for Last 12 weeks, Year Over Year

You may not need a new variable at all. Have you investigated whether one of the SAS supplied WEEK formats, WEEKU, WEEKV or WEEKW will display the value you need? The values are treated in date order and the display can show just the week number as W05 when using WEEKU3. type format (W=week), longer versions will show the year in 2 or 4 digits and the day of the week. The different formats treat the start of the first week of the year differently and whether the seek starts on Sunday or Monday.

 

See this program for brief exampl

data _null_;
   do date = '29DEC2002'd to '15JAN2003'd;
      put "Date is " date date9. +1 "Using WEEKU7: " date weeku7. +1 "Using WEEKV7: " date weekv7. +1 "Using WEEKW7: " date weekw7.;
   end;
run;

View solution in original post


All Replies
Solution
‎02-10-2016 06:26 PM
Super User
Posts: 11,113

Re: Create Week Variable for Last 12 weeks, Year Over Year

You may not need a new variable at all. Have you investigated whether one of the SAS supplied WEEK formats, WEEKU, WEEKV or WEEKW will display the value you need? The values are treated in date order and the display can show just the week number as W05 when using WEEKU3. type format (W=week), longer versions will show the year in 2 or 4 digits and the day of the week. The different formats treat the start of the first week of the year differently and whether the seek starts on Sunday or Monday.

 

See this program for brief exampl

data _null_;
   do date = '29DEC2002'd to '15JAN2003'd;
      put "Date is " date date9. +1 "Using WEEKU7: " date weeku7. +1 "Using WEEKV7: " date weekv7. +1 "Using WEEKW7: " date weekw7.;
   end;
run;
Frequent Contributor
Posts: 84

Re: Create Week Variable for Last 12 weeks, Year Over Year

Thank you!  I had to get creative with a macro variable to specify the day of the week constantly being set to yesterday, but the Week. format worked.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 278 views
  • 0 likes
  • 2 in conversation