# st: RE: egen or other methods to calculate consecutive row totals?

 From "Nick Cox" To Subject st: RE: egen or other methods to calculate consecutive row totals? Date Mon, 3 Mar 2003 19:58:12 -0000

```Alexandra de Montrichard
>
> I am somewhat new to stata and I need some help with a
> problem I can't
> seem to resolve.
>
> I am working with a database of students for whom I have quarterly
> grades for 5 years (1995-2000). I also have the start &
> completion date
> for a class that all students took a one point during those
> five year. I
> need to figure out how many quarters of grades I have for
> each student
> before & after they took this class & how many are
> consecutive quarters
> of grades. I've created a 0-1 variable for each quarter (0 if the
> student did not get any grades that quarter, 1 if they received a
> grade), converted the class start & exit date into quarter
> format & used
> egen to count of the number of quarters in which each
> However, I now need to figure out a way of counting the number of
> quarters with grades both before & after the class as well
> as a way to
> know how many consecutive quarters with grade I have after
> completion of
> the class. I though I could use egen combined with if to create a
> gnbrpost variable of the number of quarters with grades
> after completion
> of the class:
> q(1995q1)), v (1)
> q(1995q2)), v (1)
> q(1995q3)), v (1)  etc.....
>
> but I can't figure out how to replace the gnbrpost variable
> each time I
> run a new egen calculation plus I'm sure there must be a
> better way of
> doing this, which would also allow me to know how many consecutive
> quarters of grades I have.

In one sentence: -reshape- to -long- and convert your structure
into a panel data structure. Then use e.g. -tsspell-. from SSC.

should be mapped into one variable -grade-, another
-quarter- and another -student-.

. reshape long grade, i(student) j(quarter) string

-quarter- as a string variable is not handy for time series work.

. gen year = real( cond(substr(quarter,1,1)=="9",
"19" + substr(quarter,1,2),
"20" + substr(quarter,1,2)) )

. gen qtr = real(substr(quarter,-1,1))

. gen q = yq(year, qtr)
. format q %tqq_Cy

. tsset student q

Now you need to divide each student's record into
before, during and after taking a course. One
way to do it is using -tsspell- from SSC. There
are several examples in its help.

Nick
n.j.cox@durham.ac.uk

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```