topic Re: Converting week number to start date in SAS Programming

Converting week number to start date

That____Redhead — Fri, 09 Aug 2013 13:13:49 GMT

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!

Re: Converting week number to start date

LinusH — Fri, 09 Aug 2013 13:23:32 GMT

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.

Re: Converting week number to start date

That____Redhead — Fri, 09 Aug 2013 13:57:57 GMT

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!

Re: Converting week number to start date

Vince28_Statcan — Fri, 09 Aug 2013 14:09:41 GMT

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

Re: Converting week number to start date

Astounding — Fri, 09 Aug 2013 20:23:35 GMT

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.

Re: Converting week number to start date

AskoLötjönen — Mon, 12 Aug 2013 12:55:56 GMT

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;

Re: Converting week number to start date

ChrisSelley — Thu, 15 Aug 2013 15:22:06 GMT

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

Re: Converting week number to start date

Flyingbunny — Thu, 27 Aug 2020 19:13:48 GMT

Hi Vince,

I found your post from 7years ago. Your method to convert week number to start date seems very cool, but I tried and SAS gave me error message...do you have any tips to fix the error? it is at the part of the second bracket after z2. Thank YOU!

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