st: Assign observations to the right week number

["Marco Müller" <endlevel@gmx.ch>]

Dear all
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/