Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: Assign observations to the right week number


From   "Marco Müller" <endlevel@gmx.ch>
To   statalist@hsphsun2.harvard.edu
Subject   st: Assign observations to the right week number
Date   Wed, 17 Oct 2012 11:48:10 +0200

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/


© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   Site index