Hi
I have the following challenge related to creating new variables.
In my data I have daily settlement prices from a futures market. For each date (day) I have the variables JAN, FEB---- DEC (12 months). Every day there are registered prices for the next 3 months, meaning that for a given date 27dec2007 I may have observations for JAN FEB and MAR, and missing in all other months. Under is a part of these data:
mydate JAN FEB MAR
03dec2007 7.6 5.6
04dec2007 7.75 7
05dec2007 8 7.63
06dec2007 9 7.65
07dec2007 8 7.75
10dec2007 8.7 8.7
11dec2007 8.45 8.45
12dec2007 7.43 7.43
13dec2007 7.45 7.45
14dec2007 6.3 6.2
17dec2007 6.25 6.25
18dec2007 6.93 6.93
19dec2007 7.2 7.2
20dec2007 6.25 7.13
21dec2007 7 7.45
27dec2007 6.7 6.5 6
28dec2007 6.63 6.75 6
02jan2008 6.73 6
03jan2008 6.63 5.78
04jan2008 6.38 5.73
07jan2008 6.45 6.23
08jan2008 6.3 6
09jan2008 6.18 5.5
10jan2008 6.38 5.5
11jan2008 6.08 5.53
14jan2008 6.25 5.65
15jan2008 6.25 5.65
16jan2008 5.8 5.6
17jan2008 5.88 5.55
18jan2008 5.88 5.75
21jan2008 6.25 5.88
22jan2008 6.75 6.35
23jan2008 6.65 6.25
What I need to do is to create three more general variables, MONTH1-MONTH2 -MONTH3, that capture the price for the next month (the second next etc.) at a given date etc.
I was thinking that this might be done by asking Stata to look through the 12 month variables I have, and for the variable MONTH1 then replace this with the first observation not missing. Then for the Variable MONTH2 replace with the SECOND observation of the 12 variables not missing and so on--
With the displayed data above this means that MONTH1 would be 7.6 for 03Dec2007, and 6.73 for 02Jan2008, MONTH2 would be 5.6 for 03Dec2008 and 6 for 02Jan2008, etc.
My data is large, so it would be great to have a more general command to create these variables.
Hope someone is able to understand this explanation!
Thanks
Linn
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
