Ask for Help to Create Multiple-Sell and Multiple-State Data using Stata.

[smith Jason]

Hi, everybody. I am new to survival analysis. I want to create a multiple-spell & multiple-state data set for further analysis.
The dataset is layout below for illustration-
sid (student ID);csg (current student grade, If the student failed, he/she has to repeat current grade);sgr(if student had grade retention----0(No)/1(Yes));spell (spell)
*This is the expected data set (for illustration purpose, I just listed the 1st two persons).
clear
input byte (sid csg sgr spell time)
1 1 0 1 1
1 2 0 1 2
1 2 1 1 3
1 3 0 2 1
1 4 0 2 2
1 4 1 2 3
1 4 1 3 1
1 4 1 4 1
1 5 0 1 1
1 6 0 1 2
2 1 0 1 1
2 2 0 1 2
2 3 0 1 3
2 4 0 1 4
2 4 1 1 5
2 5 0 2 1
2 5 1 2 2
2 6 0 3 1
end

*This is the real dataset as below,
clear
input byte (sid csg)
1 1
1 2
1 2
1 3
1 4
1 4
1 4
1 4
1 5
1 6
2 1
2 2
2 3
2 4
2 4
2 5
2 5
2 6
3 1
3 2
3 3
3 4
3 5
3 6
4 1
4 1
4 2
4 3
4 4
4 5
4 6
end
I don't know how to use Stata code to crate the variable "sgr","spell", and "time".
By the way, how can I count the number of the students who had more than 1-time grade retentions?
Thank you for your helps!

[smith Jason]

I found there are typos in the data above, please see the correct illustrative data below,
*This is the expected data set (for illustration purpose, I just listed the 1st four persons and the whole dataset have almost 10000 persons).
clear
input byte (sid csg sgr spell time)
1 1 0 1 1
1 2 0 1 2
1 2 1 1 3
1 3 0 2 1
1 4 0 2 2
1 4 1 2 3
1 4 1 3 1
1 4 1 4 1
1 5 0 5 1
1 6 0 5 2
2 1 0 1 1
2 2 0 1 2
2 3 0 1 3
2 4 0 1 4
2 4 1 1 5
2 5 0 2 1
2 5 1 2 2
2 6 0 3 1
3 1 0 1 1
3 2 0 1 2
3 3 0 1 3
3 4 0 1 4
3 5 0 1 5
3 6 0 1 6
4 1 0 1 1
4 1 1 1 2
4 2 0 2 1
4 3 0 2 2
4 4 0 2 3
4 5 0 2 4
4 6 0 2 5
end

[Øyvind Snilsberg]

bys sid (csg): gen sgr = csg == csg[_n-1]
bys sid (csg): gen spell = cond(sgr,sum(sgr),sum(sgr)+1)
bys sid spell (csg): gen time = _n

[Clyde Schechter]

gen long seq = _n

by sid (seq), sort: gen sgr = (csg < csg[_n-1] + 1) & _n > 1
by sid (seq): gen spell = sum(sgr[_n-1] | _n == 1)
by sid spell (seq), sort: gen time = _n

Added: crossed with #3

[smith Jason]

Thank you all above who gave me kindly response!
I still don't know how to count the number of the students who have more than one time grade retentions using Stata.
As we saw, there are 2 students who repeated more than one time grade in the illustrative dataset. (Student with id=1 repeated grades at 2nd grade and 4th grade, respectively. Similarly, student with id=2 repeated grades at 4th grade and 5th grade, respectively. In contrast, student with id=3 made grade progress normally while student with id=4 repeated grade at 1st grade). Thank you for your assistances!

[Clyde Schechter]

by sid (spell): gen multiple_retentions = spell[_N] > 2

[smith Jason]

Thank you!