Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# Re: st: new variables creation with unbalanced panel

 From Ulrich Kohler To statalist@hsphsun2.harvard.edu Subject Re: st: new variables creation with unbalanced panel Date Wed, 13 Oct 2010 09:17:47 +0200

```Am Dienstag, den 12.10.2010, 19:41 -0700 schrieb Laura:
> Hi, I am very new at using STATA. Sorry for the previous post, in which the
> panel looked all wrong.
> I am using an unbalanced panel of the form:
>
> country year x y diff_x average_y initial_x
> A 1 xA1 yA1
> A 2 xA2 yA2 xA2-xA1 AVG(yA2:yA1) xA1
> A 3  yA3
> A 4  yA4
> A 5 xA5 yA5 xA5-xA2 AVG(yA5:yA2) xA2
> B 1  yB1
> B 2 xB2 yB2
> B 3  yB3
> B 4 xB4 yB4 xB4-xB2 AVG(yB4:YB2) xB2
>
> Basically, variable x has many missing values while y is available for all
> years.
>
> I need to create 3 types of new variables (as in the last 3 columns of the
> table):
>
> 1) a variable that for each country will equal the difference between two
> consecutive available x values.(diff_x)
> 2) a variable that for each country will take the value the average of all y's
> available between  two consecutive available x values. (average_y)
> 3) a variable that for each country, for each available x takes the value of the
>
> previous observed x. (initial_x)

Laura,

Something like this:

------------------------example.do
clear
input ctr year x y
1 1  2 3
1 2  5 7
1 3  3 4
1 4  . 6
1 5  4 6
2 1  . 5
2 2  3 2
2 3  . 4
2 4  1 7
end

tsset ctr year

gen diff_x = d1.x

by ctr (year), sort: ///
gen isconseq = (x<. & x[_n+1] <.) | (x<. & x[_n-1] <.)

egen avgy = mean(y) if isconseq

gen initial_x = l1.x

--------------------------------------

More details are in -help tsvarlist- and -help egen-. Being new to Stata
I would also recommend to learn about Recoding with -by- and explicit
subscripts. Section 5.1.3 in Kohler/Kreuter, Data Analysis Using Stata,
2nd edtion is one place to start.

Many regards
Uli

*
*   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/
```