Filling data into new variable constrained by specific dates

[Clarice Martins]

Hello Statalist members!

I am working with Stata 12.1 for Mac.

And I am trying to copy a number variable -Z- that happens in Dec of t-1, to a new variable -Zlag- in all observations that happen Jul of t thru Jun of t+1, by group of companies.

I tried something like this...

gen Zlag = .
by co_id: replace Zlag = Z[_n-18]

But, got stuck in the case below (sample table), for instance, where:
- data didn't go back before 18 lags (i.e.: 2000m8), but,
- since what i need is: Z of DEC of 2000, to be copied into JUL of 2001 thru JUN of 2002,
- my code did not fill JUL 2001 thru DEC 2001

(In the spirit of this paper http://www.stata.com/support/faqs/da...ssing-values/:
I tried messing around with year constrains and/or missing constrains, but got so confused, that I think I'd better no post. Everything i tried gave me syntax errors.)

I hope I am being clear... I have a real hard time putting in writing my doubts.

Thanks for any advices!!

Regards,
Clarice

 
co_id
ticker
period
use_period
year
Y
Z
Zlag

2
ABEV3 BZ Equity
31-Aug-00
2000m8
2000
-0.1221399
0.18


2
ABEV3 BZ Equity
29-Sep-00
2000m9
2000
0.1764253
0.18


2
ABEV3 BZ Equity
31-Oct-00
2000m10
2000
0.0768052
0.18


2
ABEV3 BZ Equity
30-Nov-00
2000m11
2000
0.0346449
0.18


2
ABEV3 BZ Equity
28-Dec-00
2000m12
2000
-0.0115395
0.18


2
ABEV3 BZ Equity
31-Jan-01
2001m1
2001
0.0426288
0


2
ABEV3 BZ Equity
28-Feb-01
2001m2
2001
-0.0392467
0


2
ABEV3 BZ Equity
30-Mar-01
2001m3
2001
0.0712054
0


2
ABEV3 BZ Equity
30-Apr-01
2001m4
2001
-0.0254112
0


2
ABEV3 BZ Equity
31-May-01
2001m5
2001
-0.0622875
0


2
ABEV3 BZ Equity
29-Jun-01
2001m6
2001
0.035373
0


2
ABEV3 BZ Equity
31-Jul-01
2001m7
2001
-0.0455059
0


2
ABEV3 BZ Equity
31-Aug-01
2001m8
2001
0.0023007
0


2
ABEV3 BZ Equity
28-Sep-01
2001m9
2001
0.0035542
0


2
ABEV3 BZ Equity
31-Oct-01
2001m10
2001
-0.0390703
0


2
ABEV3 BZ Equity
30-Nov-01
2001m11
2001
0.0169988
0


2
ABEV3 BZ Equity
28-Dec-01
2001m12
2001
-0.0587633
0


2
ABEV3 BZ Equity
31-Jan-02
2002m1
2002
-0.0060419
0.15
0.1787597

2
ABEV3 BZ Equity
28-Feb-02
2002m2
2002
-0.0770768
0.15
0.1787597

2
ABEV3 BZ Equity
28-Mar-02
2002m3
2002
-0.0611098
0.15
0.1787597

2
ABEV3 BZ Equity
30-Apr-02
2002m4
2002
0.0658515
0.15
0.1787597

2
ABEV3 BZ Equity
31-May-02
2002m5
2002
-0.0481699
0.15
0.1787597

2
ABEV3 BZ Equity
28-Jun-02
2002m6
2002
-0.0050798
0.15
0.1787597

2
ABEV3 BZ Equity
31-Jul-02
2002m7
2002
0.0412485
0.15
0

2
ABEV3 BZ Equity
30-Aug-02
2002m8
2002
-0.0496834
0.15
0

2
ABEV3 BZ Equity
30-Sep-02
2002m9
2002
0.0729031
0.15
0

2
ABEV3 BZ Equity
31-Oct-02
2002m10
2002
0.0632335
0.15
0

2
ABEV3 BZ Equity
29-Nov-02
2002m11
2002
0.0048092
0.15
0

2
ABEV3 BZ Equity
30-Dec-02
2002m12
2002
-0.0826399
0.15
0

2
ABEV3 BZ Equity
31-Jan-03
2003m1
2003
-0.0464636
0.2
0

2
ABEV3 BZ Equity
28-Feb-03
2003m2
2003
0.0144948
0.2
0

2
ABEV3 BZ Equity
31-Mar-03
2003m3
2003
0.0447779
0.2
0

2
ABEV3 BZ Equity
30-Apr-03
2003m4
2003
-0.0231831
0.2
0

2
ABEV3 BZ Equity
30-May-03
2003m5
2003
-0.0338318
0.2
0

2
ABEV3 BZ Equity
30-Jun-03
2003m6
2003
-0.0014623
0.2
0

2
ABEV3 BZ Equity
31-Jul-03
2003m7
2003
-0.0475044
0.2
0.1516507

[FernandoRios]

Hi Clarice,
One word of advice. What exactly you want to use to "fill" the observations between July and December 2001. In your code, you are asking Stata to fill the missing variables with whatever happen 18 months in the past. Thus is expected that the first 18 months have no information (missing) as there is nothing to look for beyond August 2000.
HTH
Fernando

[Clarice Martins]

Hey, Fernando!!
Thank you so much for your feedback.

I guess... my poor coding ability misguided you.

What I need is: to copy a variable -Z- that happens in Dec of t-1, to a new variable -Zlag- in all observations that happen Jul of year t thru Jun of year t+1, by group of companies.

Variable Z is a variable which I need to use in a lag form as explained above.

Thanks for any suggestions.

My apologies for not explaining quite well.

Thanks!!