DATA Step, Macro, Functions and more

Converting week number to start date

Reply
Contributor
Posts: 25

Converting week number to start date

Hey,

I have been aggregating data by the week for a reporting suite I'm putting together, and in part of this I have to report on weekly sales by sales channel, on a Monday-Sunday week.  So I use the function WEEK(<var>,"w") to pull the week number.  But I have such few sales by sales channel, that there may or may not be a sale every Monday.

So I am having to create a separate reference table with week number and week start date (using proc sql and min(order_date) grouped by week(order_date,"w").

Is there some way to embed an intnx function with the week function or so, so that I can always return the first date of a week number?  Or some other function I just don't know about?

Thanks a ton!

Super User
Posts: 5,424

Re: Converting week number to start date

Posted in reply to That____Redhead

I don't think I understand why you need the start date of the week for your application.

Nevertheless, I don't know of any generic way to get the starting day based on the week number.

But to create a lookup table, sure, that should be no problem.

Just start by generating every possible (mon)day, and then generate the week no based on that.

Data never sleeps
Contributor
Posts: 25

Re: Converting week number to start date

I was hoping to avoid a reference table, and I need the date just to make logical sense of the data on the graphs.  Yeah there are other ways to do it, but if I could get it to export the week start date, it'd just be one less step, you know?

Thanks for your feedback!    

Super Contributor
Posts: 339

Re: Converting week number to start date

Posted in reply to That____Redhead

You minimally need week number and year to be able to achieve desired results as the start date of a given week varies every year. At least as far as I can tell.

From weekvw. informat documentation:

The WEEKVw. informat reads a
format of the number-of-week value. If the input does not contain a year
expression, WEEKVw. uses the current year as the year
expression, which is the default. If the input does not contain a day
expression, WEEKVw. uses the first day of the week as
the day expression, which is the default.

Here's how it can be done if you have both in variables. You can replace the put(yearnum, 4.) by "2013" or whatever if your year is fixed accross your entire dataset and don't have a year variable.

data have;

     input dt date9.;

     datalines;

01JAN2011

02JAN2011

03JAN2011

04JAN2011

05JAN2011

06JAN2011

07JAN2011

08JAN2011

;

run;

data want;

     set have;

     weeknum=week(dt, "w");

     yearnum=year(dt);

     weekstartdate=input(put(yearnum, 4.)||"W"||put(weeknum,z2.))||"01", weekv9.);

run;

Vince

Super User
Posts: 5,497

Re: Converting week number to start date

Posted in reply to That____Redhead

If I understand correctly, you already have a SAS date stored in VAR, and you want the SAS date that corresponds to the most recent Monday.  If that's the case, the week number doesn't matter.  You can calculate what you need based on VAR.  Here's one way:

week_begins = var - weekday(var) + 1 - 7*(weekday(var)=0);

Perhaps I'm solving the wrong problem??

Good luck.

Contributor
Posts: 44

Re: Converting week number to start date

Posted in reply to That____Redhead

I think that using Intnx-function is the easiest way to solve this. With 'Week.2' you are setting your weeks starting from Monday:

data want;

  set have;

  monday=intnx('week.2',dt,0);

  week=week(monday);

run;


Occasional Contributor
Posts: 15

Re: Converting week number to start date

Posted in reply to AskoLötjönen

If i understand you correctly, you don't really want week number but the week start or the previous Monday's date in each of your records.

if you have a variable DATE for the date of each sale then

if weekday(date)=1 then weekstart=date-6;else

                                   weekstart=date-weekday(date)+2;

will put the previous Mondays date in weekstart

Hope it's what you need

Ask a Question
Discussion stats
  • 6 replies
  • 6255 views
  • 0 likes
  • 6 in conversation