Re: st: Assign observations to the right week number

[Nick Cox <njcoxstata@gmail.com>]

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" <endlevel@gmx.ch> 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/