BookmarkSubscribeRSS Feed
That____Redhead
Fluorite | Level 6

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!

7 REPLIES 7
LinusH
Tourmaline | Level 20

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
That____Redhead
Fluorite | Level 6

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!    

Vince28_Statcan
Quartz | Level 8

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

Flyingbunny
Calcite | Level 5

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.);

Astounding
PROC Star

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.

AskoLötjönen
Quartz | Level 8

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;


ChrisSelley
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 7 replies
  • 13856 views
  • 0 likes
  • 7 in conversation