Date: Tue, 11 Aug 2009 18:21:01 -0500
Reply-To: OR Stats <stats112@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: OR Stats <stats112@GMAIL.COM>
Subject: Re: Split Dataset into 1st, 2nd, 3rd, 4th week of the month
In-Reply-To: <6eca73440908110923r59443f6bs7ae49e7a8f15ee31@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
I have since played more with the second dataset so that it is one step
closer.
second table
StartDate TermDate
..
..
6-25-09 7-24-09
7-27-09 8-25-09
..
In my original table, however, I don't have a single variable linker to
merge these two datasets together (e.g., product id). So that the problem
now would be two steps
1. For each date (record) in the original data, I would find and match the
appropriate range (StartDate,EndDate ), or record in the second dataset
2. For each date, determine if it falls within the 1st/2nd/3rd/4th week of
that range.
Step 2 was also accomplished earlier in Kevin's email. But I'm not sure how
Step 1 can be done elegantly
On Tue, Aug 11, 2009 at 11:23 AM, OR Stats <stats112@gmail.com> wrote:
> That's good. So if my original dataset is organized by date and my second
> date set is just a list of termination dates. What is the simplest way to
> have each record (business date) associate itself with the correct product
> termination date? In other words, in my original datset, I do not have a
> column that notes which product is sold at that price Y and volume V. It
> would be implied out from my second dateset with the termination date of the
> monthly product.
>
> E.g.,
> Original
> Date Y V
> 7-20-09
> .
> .
> 7-27-09
> .
> .
> 8-11-09
> .
> .
>
> second table
> TermDate
> 7-24-09
> 8-25-09
>
> Needed table
> Date TermDate Wk Y V
> 7-20-09 7-24-09 4
> 7-31-09 8-25-09 1
> .
> .
> 8-14-09 8-25-09 3
> .
>
>
> Note there are many more records in the original table (calendar work days)
> then in the second table which is just one date per month.
>
> Thank you!
> On Thu, Jul 30, 2009 at 11:42 AM, Kevin Myers <KevinMyers@austin.rr.com>wrote:
>
>> Your actual problem is really much easier to solve than what I assumed
>> previously. You mention that your data includes a termination date for each
>> product. Does it also contain a start date for each product? If not, you
>> will need to include code to come up with that. Once you have the start
>> date, then you can simply use the following to calculate your week value:
>>
>> week = 1 + int(date - startDate) / 7; /* SAS date values must be used for
>> this to work as provided. */
>>
>> If your data only includes a sale date and a termination date, then
>> appropriate data step code might look something like this:
>>
>> proc sort data=sales; by saleDate; run;
>>
>> data sales;
>> set sales; by terminationDate; /* Note: Sort by sale date also implies
>> data is sorted by termination date. */
>> if first.terminationDate then startDate=saleDate;
>> week = 1 + int(date - startDate)/7;
>> retain startDate;
>> run;
>>
>>
>> ----- Original Message ----- From: "OR Stats" <stats112@GMAIL.COM>
>> To: <SAS-L@LISTSERV.UGA.EDU>
>> Sent: Thursday, July 30, 2009 11:17
>>
>> Subject: Re: Split Dataset into 1st, 2nd, 3rd, 4th week of the month
>>
>>
>> Hi Dan: Thanks for your question, I am thinking about the problem more
>>> carefully and need to revise the definition to be more accurate...
>>>
>>> My data is daily historic product volume. However, the daily volume
>>> references a new product each month. When one product offering ends
>>> towards
>>> the end of the month, a new product offering is open. I am interested in
>>> studying the volume in the first, second, third, and fourth week from
>>> when a
>>> new product is launched over time.
>>>
>>> Each month, I have a *termination date* (for when the current month's
>>> product offer ends) that falls on a workday date that is near to the end
>>> of
>>> the month. The termination date is *variable*, but known for my entire
>>> daily dataset.
>>>
>>> So 'monthly' product is a soft defn of month, because for
>>>
>>> 'Oct' 2009 product: volume is collected for this offer beginning Sep 29 -
>>> Oct 28, 2009 = 22 days
>>> 'Nov' 2009 product: volume is collected for this offering beginning Oct
>>> 29 -
>>> Nov 24, 2009 = 19 days
>>>
>>> For my daily volume data for the 'Oct' product offering, I would like to
>>> divide Sep 29-Oct 28 into *day1-5, day6-10, day11-15*, *day16-*22.
>>> For my daily volume data for the 'Nov' product offering, I would like to
>>> divide Oct 29-Nov 24 into day1-5, day6-10, day11-15, day16-19.
>>>
>>> Of course, I have no volumes on *weekends *and *holidays*, so in my
>>> dataset
>>> the dates are not absolutely continuous to code simply +5 for example.
>>>
>>> For every month product, the range of dates for the 5 volume days are
>>> variable depending on weekends/holidays. This makes it tricky.
>>>
>>> I would greatly appreciate all your suggestions as the problem of coding
>>> this is boggling.
>>> On Thu, Jul 30, 2009 at 10:21 AM, Nordlund, Dan (DSHS/RDA) <
>>> NordlDJ@dshs.wa.gov> wrote:
>>>
>>> > -----Original Message-----
>>>> > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>>>> OR
>>>> > Stats
>>>> > Sent: Thursday, July 30, 2009 8:07 AM
>>>> > To: SAS-L@LISTSERV.UGA.EDU
>>>> > Subject: Re: Split Dataset into 1st, 2nd, 3rd, 4th week of the month
>>>> >
>>>> > Thanks, Kevin for pointing that out. The defn for our purposes is
>>>> >
>>>> > If the 1st of the month falls during the business week (Mon, Tue, ..
>>>> Fri),
>>>> > the first week of the month is the week of the 1st.
>>>> >
>>>> > If the 1st of the month falls during the weekend (Sat or Sun), the >
>>>> first
>>>> > week of the month is the full week there following
>>>> > .
>>>> > So take two extreme cases
>>>> >
>>>> > E.g., Jun 1, 2009 was on a Monday
>>>> > Mon 6/1- Fri 6/5 is the 1st work week
>>>> > Mon 6/8- Fri 6/12 is the 2nt work week
>>>> > Mon 6/15 - Fri 6/19 is the 3rd work week
>>>> > Mon 6/22 - Fri 6/26 is the 4th work week
>>>> >
>>>> > Aug 1, 2009 is on a Saturday
>>>> > Mon 8/3 - Fri 8/7 is the 1st work week
>>>> > Mon 8/10 - Fri 8/14 is the 2nd work week
>>>> > Mon 8/17 - Fri 8/21 is the 3rd work week
>>>> > Mon 8/24 - Fri 8/28 is the 4th work week
>>>> >
>>>> > Would greatly appreciate the syntax in coding this with respect to the
>>>> dates
>>>> > that I have.
>>>>
>>>> So, just to be clear, for October 2009 what days should be included in
>>>> the
>>>> first week of the month?
>>>>
>>>> Dan
>>>>
>>>> Daniel J. Nordlund
>>>> Washington State Department of Social and Health Services
>>>> Planning, Performance, and Accountability
>>>> Research and Data Analysis Division
>>>> Olympia, WA 98504-5204
>>>>
>>>>
>>
>
| 
