08-09-2013 09:13 AM
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!
08-09-2013 09:23 AM
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.
08-09-2013 09:57 AM
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!
08-09-2013 10:09 AM
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.
input dt date9.;
weekstartdate=input(put(yearnum, 4.)||"W"||put(weeknum,z2.))||"01", weekv9.);
08-09-2013 04:23 PM
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??
08-12-2013 08:55 AM
I think that using Intnx-function is the easiest way to solve this. With 'Week.2' you are setting your weeks starting from Monday:
08-15-2013 11:22 AM
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
will put the previous Mondays date in weekstart
Hope it's what you need