Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: Assign observations to the right week number
From
Nick Cox <[email protected]>
To
[email protected]
Subject
Re: st: Assign observations to the right week number
Date
Wed, 17 Oct 2012 11:45:08 +0100
Naturally, the method is essentially the same for any other definition
of the week. "+3" and "-4" will be different constants. Also, the week
identifiers go up in 7s, but can easily be scaled to any sequence
going up in 1s.
Nick
On Wed, Oct 17, 2012 at 11:14 AM, Nick Cox <[email protected]> wrote:
> I take it that you mean that a week starts on Wednesday (first day)
> and ends on Tuesday (last day).
>
> This explanation includes things you do know already: I am writing it
> out step by step because it may be helpful to others.
>
> In your case, each week can be characterised by the Wednesday that
> starts it (or the Tuesday that ends it, etc.). I will take the former.
>
> As we write, it's a Wednesday today and so the start of a week by that
> definition.
>
> . di dow(mdy(10,17,2012))
> 3
>
> Stata's -dow()- function, given daily dates, returns 3 for Wednesday
> (0 = Sunday).
>
> Build a small sandpit of dates centred on today
>
> . clear
>
> . set obs 7
> obs was 0, now 7
>
> . gen date = mdy(10,17,2012) + (_n - 4)
>
> . format date %td
>
> . l, sep(0)
>
> +-----------+
> | date |
> |-----------|
> 1. | 14oct2012 |
> 2. | 15oct2012 |
> 3. | 16oct2012 |
> 4. | 17oct2012 |
> 5. | 18oct2012 |
> 6. | 19oct2012 |
> 7. | 20oct2012 |
> +-----------+
>
> For today through to Saturday, today is the start of the week, but for
> yesterday back to Sunday it's a week earlier. Some general code is
> given by
>
> gen week_start = cond(dow(date) >= 3, date - dow(date) + 3, date -
> dow(date) - 4)
>
> . format week_start %td
>
> . l, sep(0)
>
> +-----------------------+
> | date week_st~t |
> |-----------------------|
> 1. | 14oct2012 10oct2012 |
> 2. | 15oct2012 10oct2012 |
> 3. | 16oct2012 10oct2012 |
> 4. | 17oct2012 17oct2012 |
> 5. | 18oct2012 17oct2012 |
> 6. | 19oct2012 17oct2012 |
> 7. | 20oct2012 17oct2012 |
> +-----------------------+
>
> Once you have a variable -week_start- it's the same for all days in
> the same week, and could be used for summary or aggregation.
>
> With this approach, first there is no need even for dates to be in
> sequence; also whether there are gaps in the data does not matter at
> all.
>
> For more discussion, see
>
> Cox, N.J. 2010. Stata tip 68: Week assumptions. Stata Journal 10(4): 682-685
>
> Cox, N.J. 2012. Stata tip 111: More on working with weeks. Stata
> Journal 12(3): 565-569.
>
>
> On Wed, Oct 17, 2012 at 10:48 AM, "Marco Müller" <[email protected]> wrote:
>
>> I have to calculate weekly returns (from Wednesday to Wednesday) from daily returns for several companies. Therefore, I assigned the observations to week numbers per company.
>>
>> The problem is that some dates are missing, e.g. there is a week (from Wed to Wed) where only 4 entries exist.
>> I managed to assign observations to the right week if there are days other than Wednesday (Mon, Tue, Thu, Fri, Sat, Sun) are missing.
>> However, if Wednesday (= the cutoff day) is missing, Tuesdays should be chosen as cutoff day (if Tuesday is missing too, Monday should be chosen). With my code, it assigns all days to the same week number until the next Wednesday is available (see observations 16-22) Unfortunately I get stuck with that problem.
>>
>> Example & code are provided below (I'm sure there is a nicer way to compute it - i tried it with a nested forvalue expression - but I didn't manage it).
>>
>>
>> Thank you very much for your help.
>>
>> Best regards,
>> Marco
>>
>>
>> *---- Code example ---*
>> gen dow=dow(date)
>> gen check=1 if dow==3
>> sort id check date
>> by id: gen n=_n if check==1
>> gen N=.
>>
>> by id: replace N=n[_n-1] if n==. & n[_n-1]!=.
>> by id: replace N=n[_n-2] if n==. & n[_n-1]==. & n[_n-2]!=0
>> by id: replace N=n[_n-3] if n==. & n[_n-1]==. & n[_n-2]==. & n[_n-3]!=0
>> by id: replace N=n[_n-4] if n==. & n[_n-1]==. & n[_n-2]==. & n[_n-3]==. & n[_n-4]!=0
>> by id: replace N=n[_n-5] if n==. & n[_n-1]==. & n[_n-2]==. & n[_n-3]==. & n[_n-4]==. & n[_n-5]!=0
>> by id: replace N=n[_n-6] if n==. & n[_n-1]==. & n[_n-2]==. & n[_n-3]==. & n[_n-4]==. & n[_n-5]==. & n[_n-6]!=0
>> by id: replace N=N[_n-1] if n!=.
>> *---- Code ends ----*
>>
>>
>>
>>
>> id = company, date=date, ret=return, dow= day of the week, check= Wednesday-dummy, n = week number per company, N = assigns days to the right week
>>
>> +---------------------------------------------------+
>> | id date ret dow check n N |
>> |---------------------------------------------------|
>> 1. | 1 16/10/2012 -.010101 2 . . . |
>> 2. | 1 18/10/2012 .020202 4 . . 1 |
>> 3. | 1 19/10/2012 .009901 5 . . 1 |
>> 4. | 1 22/10/2012 -.0196078 1 . . 1 |
>> 5. | 1 23/10/2012 .02 2 . . 1 |
>> |---------------------------------------------------|
>> 6. | 1 24/10/2012 .0196078 3 1 2 1 |
>> 7. | 2 25/10/2012 .0192308 4 . . 2 |
>> 8. | 2 26/10/2012 .0188679 5 . . 2 |
>> 9. | 2 29/10/2012 -.0185185 1 . . 2 |
>> 10. | 2 30/10/2012 -.0283019 2 . . 2 |
>> |---------------------------------------------------|
>> 11. | 2 31/10/2012 -.0194175 3 1 3 2 |
>> 12. | 2 02/11/2012 -.019802 5 . . 3 |
>> 13. | 2 05/11/2012 -.010101 1 . . 3 |
>> 14. | 2 07/11/2012 -.0204082 3 1 4 3 |
>> 15. | 2 09/11/2012 -.03125 5 . . 4 |
>> |---------------------------------------------------|
>> 16. | 2 12/11/2012 .0430108 1 . . 4 |
>> 17. | 2 13/11/2012 .0206186 2 . . 4 |
>> 18. | 2 15/11/2012 .020202 4 . . 4 |
>> 19. | 2 16/11/2012 -.009901 5 . . 4 |
>> 20. | 2 19/11/2012 -.01 1 . . 4 |
>> |---------------------------------------------------|
>> 21. | 2 20/11/2012 -.010101 2 . . . |
>> 22. | 2 21/11/2012 -.0204082 3 1 5 . |
>> +---------------------------------------------------+
>>
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/faqs/resources/statalist-faq/
* http://www.ats.ucla.edu/stat/stata/