working on attendance data over weeks

[Yuba Raj]

Hi All, I am new to panel data analysis. I am trying to analyse data involving attendance over 12 weeks program



subid progwk ynattend x2temp keep x3temp early_~d

1. 101 1 1 6 1 . .
2. 101 5 1 . 9 5 .
3. 102 1 1 2 1 . .
4. 102 5 1 . 9 8 .
5. 103 1 0 4 1 . .


I wanted to create a variable (early attender: last variable) to describe those who attended 5 weeks and left attending.
I created x2temp and x3temp to sum number of attendance over 12 weeks . I used 8 sessions in 5 weeks (5 weeks*2 sessions) to be a marker of good attendance.
I used following commands

egen x2temp = sum(ynattend)if progwk<5, by(subid)

bysort subid : gen keep = _n

egen x3temp = sum(ynattend)if progwk>=5, by(subid)
keep if keep==1 | keep==9
gen early_attend=.
replace early_attend=1 if x2temp>=8 & x3temp==0

However, It does not work as I wanted. Stata codes early attend=1 only where x3temp=0 ignoring x2temp values because x2temp values for that person is in different row.
How do we ask stata to look for values when observations are in two seperate rows because subject ID is repeated.
Am I doing the right way, what could be a easier way to do this.
I am also trying to find people who attended late weeks but did not attend early weeks

Thank you.

[William Lisowski]

In answering your previous question, I asked that you improve your future presentation by reviewing the Statalist FAQ.

Do now please follow that guidance and review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you have used to create your two posst. Note especially sections 9-12 on how to best pose your question.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

This new problem is sufficiently complex that whoever helps you will want to be able to test their code using a good example of your data.

Please be sure to use the dataex command to show your example data. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex and read the simple instructions for using it. dataex will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use dataex.

[Yuba Raj]

Thank you for the suggestions. I am using Stata 15.1

This was the data before I selected by typing keep command : keep if keep==1 | keep==9

input int subid byte(prgwk session_num attendance notattend) float(ynattend x2temp keep x3temp)
101 1 2 1 . 1 6 1 .
101 1 1 1 . 1 6 2 .
101 2 1 1 . 1 6 3 .
101 2 2 2 1 0 6 4 .
101 3 2 1 . 1 6 5 .
101 3 1 1 . 1 6 6 .
101 4 2 1 . 1 6 7 .
101 4 1 2 1 0 6 8 .
101 5 2 1 . 1 . 9 5
101 5 1 1 . 1 . 10 5
101 6 1 2 1 0 . 11 5
101 6 2 2 1 0 . 12 5
101 7 1 1 . 1 . 13 5
101 7 2 2 1 0 . 14 5
101 8 1 1 . 1 . 15 5
101 8 2 2 1 0 . 16 5
101 9 1 2 1 0 . 17 5
101 9 2 2 1 0 . 18 5
101 10 1 2 1 0 . 19 5
101 10 2 2 1 0 . 20 5
101 11 2 2 1 0 . 21 5
101 11 1 2 1 0 . 22 5
101 12 1 2 1 0 . 23 5
101 12 2 1 . 1 . 24 5
102 1 1 1 . 1 2 1 .
102 1 2 2 1 0 2 2 .
102 2 2 2 1 0 2 3 .
102 2 1 2 1 0 2 4 .
102 3 2 2 1 0 2 5 .
102 3 1 2 1 0 2 6 .
102 4 1 2 1 0 2 7 .
102 4 2 1 . 1 2 8 .
102 5 1 1 . 1 . 9 8
102 5 2 1 . 1 . 10 8
102 6 2 2 1 0 . 11 8
102 6 1 2 1 0 . 12 8
102 7 2 2 1 0 . 13 8
102 7 1 2 1 0 . 14 8
102 8 1 2 1 0 . 15 8
102 8 2 2 1 0 . 16 8
102 9 1 2 1 0 . 17 8
102 9 2 1 . 1 . 18 8
102 10 2 1 . 1 . 19 8
102 10 1 1 . 1 . 20 8
102 11 1 2 1 0 . 21 8
102 11 2 1 . 1 . 22 8
102 12 2 1 . 1 . 23 8
102 12 1 1 . 1 . 24 8
103 1 2 2 1 0 4 1 .
103 1 1 1 . 1 4 2 .
103 2 2 2 1 0 4 3 .
103 2 1 1 . 1 4 4 .
103 3 1 1 . 1 4 5 .
103 3 2 2 1 0 4 6 .
103 4 2 1 . 1 4 7 .
103 4 1 2 1 0 4 8 .
103 5 2 2 1 0 . 9 1
103 5 1 1 . 1 . 10 1
103 6 2 2 1 0 . 11 1
103 6 1 2 1 0 . 12 1
103 7 1 2 1 0 . 13 1
103 7 2 2 1 0 . 14 1
103 8 1 2 1 0 . 15 1
103 8 2 2 1 0 . 16 1
103 9 2 2 1 0 . 17 1
103 9 1 2 1 0 . 18 1
103 10 1 2 1 0 . 19 1
103 10 2 2 1 0 . 20 1
103 11 1 2 1 0 . 21 1
103 11 2 2 1 0 . 22 1
103 12 1 2 1 0 . 23 1
103 12 2 2 1 0 . 24 1
104 1 1 1 . 1 3 1 .
104 1 2 2 1 0 3 2 .
104 2 2 2 1 0 3 3 .
104 2 1 2 1 0 3 4 .
104 3 2 2 1 0 3 5 .
104 3 1 2 1 0 3 6 .
104 4 1 1 . 1 3 7 .
104 4 2 1 . 1 3 8 .
104 5 1 1 . 1 . 9 8
104 5 2 1 . 1 . 10 8
104 6 2 2 1 0 . 11 8
104 6 1 2 1 0 . 12 8
104 7 2 2 1 0 . 13 8
104 7 1 2 1 0 . 14 8
104 8 1 2 1 0 . 15 8
104 8 2 2 1 0 . 16 8
104 9 1 2 1 0 . 17 8
104 9 2 1 . 1 . 18 8
104 10 2 1 . 1 . 19 8
104 10 1 1 . 1 . 20 8
104 11 2 1 . 1 . 21 8
104 11 1 2 1 0 . 22 8
104 12 1 1 . 1 . 23 8
104 12 2 1 . 1 . 24 8
105 1 1 1 . 1 7 1 .
105 1 2 1 . 1 7 2 .
105 2 2 1 . 1 7 3 .
105 2 1 1 . 1 7 4 .
end


After I selected cases the data by using 5 weeks as a cut-off week it would be like this

clear
input int subid byte(prgwk session_num attendance notattend) float(ynattend x2temp keep x3temp early_attend)
101 1 2 1 . 1 6 1 . .
101 5 2 1 . 1 . 9 5 .
102 1 1 1 . 1 2 1 . .
102 5 1 1 . 1 . 9 8 .
103 1 2 2 1 0 4 1 . .
103 5 2 2 1 0 . 9 1 .
104 1 1 1 . 1 3 1 . .
104 5 1 1 . 1 . 9 8 .
105 1 1 1 . 1 7 1 . .
105 5 1 2 1 0 . 9 6 .
106 1 2 1 . 1 8 1 . .
106 5 1 1 . 1 . 9 12 .
107 1 1 1 . 1 3 1 . .
107 5 2 2 1 0 . 9 1 .
108 1 2 1 . 1 7 1 . .
108 5 2 1 . 1 . 9 12 .
109 1 1 1 . 1 5 1 . .
109 5 1 2 1 0 . 9 8 .
110 1 1 1 . 1 3 1 . .
110 5 1 1 . 1 . 9 13 .
111 1 2 2 1 0 3 1 . .
111 5 2 1 . 1 . 9 13 .
112 1 2 1 . 1 5 1 . .
112 5 2 2 1 0 . 9 1 .
113 1 1 1 . 1 8 1 . .
113 5 2 1 . 1 . 9 14 .
114 1 1 1 . 1 6 1 . .
114 5 1 1 . 1 . 9 13 .
115 1 2 1 . 1 6 1 . .
115 5 1 1 . 1 . 9 7 .
116 1 1 1 . 1 6 1 . .
116 5 1 1 . 1 . 9 9 .
117 1 1 1 . 1 4 1 . .
117 5 2 2 1 0 . 9 1 .
118 1 1 1 . 1 8 1 . .
118 5 2 1 . 1 . 9 10 .
119 1 1 1 . 1 6 1 . .
119 5 1 1 . 1 . 9 7 .
120 1 2 1 . 1 7 1 . .
120 5 2 2 1 0 . 9 11 .
121 1 2 1 . 1 7 1 . .
121 5 1 1 . 1 . 9 13 .
122 1 1 1 . 1 5 1 . .
122 5 2 1 . 1 . 9 6 .
123 1 2 2 1 0 4 1 . .
123 5 1 1 . 1 . 9 2 .
124 1 2 2 1 0 6 1 . .
124 5 2 1 . 1 . 9 11 .
125 1 1 1 . 1 8 1 . .
125 5 1 1 . 1 . 9 7 .
126 1 1 1 . 1 4 1 . .
126 5 1 1 . 1 . 9 11 .
127 1 2 2 2 0 1 1 . .
127 5 1 2 2 0 . 9 0 .
128 1 1 1 . 1 7 1 . .
128 5 2 1 . 1 . 9 8 .
129 1 1 1 . 1 3 1 . .
129 5 1 1 . 1 . 9 7 .
130 1 1 1 . 1 7 1 . .
130 5 1 1 . 1 . 9 5 .
131 1 1 1 . 1 4 1 . .
131 5 2 1 . 1 . 9 14 .
132 1 2 1 . 1 4 1 . .
132 5 1 1 . 1 . 9 10 .
133 1 2 1 . 1 4 1 . .
133 5 2 2 1 0 . 9 0 .
134 1 1 1 . 1 4 1 . .
134 5 2 2 1 0 . 9 0 .
135 1 2 1 . 1 5 1 . .
135 5 2 2 1 0 . 9 2 .
136 1 1 1 . 1 5 1 . .
136 5 2 2 1 0 . 9 2 .
137 1 2 1 . 1 5 1 . .
137 5 2 1 . 1 . 9 6 .
138 1 1 1 . 1 6 1 . .
138 5 1 2 1 0 . 9 6 .
139 1 2 1 . 1 8 1 . .
139 5 2 2 1 0 . 9 8 .
140 1 1 2 1 0 6 1 . .
140 5 1 1 . 1 . 9 13 .
142 1 1 2 1 0 6 1 . .
142 5 2 2 1 0 . 9 7 .
143 1 1 2 1 0 2 1 . .
143 5 1 2 1 0 . 9 0 .
144 1 2 1 . 1 4 1 . .
144 5 1 2 1 0 . 9 0 .
145 1 2 2 1 0 1 1 . .
145 5 1 1 . 1 . 9 3 .
146 1 1 2 1 0 1 1 . .
146 5 1 1 . 1 . 9 3 .
147 1 1 2 1 0 2 1 . .
147 5 2 2 1 0 . 9 1 .
148 1 2 2 1 0 4 1 . .
148 5 1 2 1 0 . 9 4 .
149 1 2 2 1 0 5 1 . .
149 5 2 1 . 1 . 9 8 .
150 1 2 2 1 0 3 1 . .
150 5 1 2 1 0 . 9 2 .
151 1 1 2 1 0 6 1 . .
151 5 1 1 . 1 . 9 12 .
end
Thanks again

[William Lisowski]

Thank you for the better sample of data. I have reposted it below as it is presented by dataex, include the CODE delimiters that are part of the output and which serve to make the output easily readable.

I'm in the middle of something else at the moment and cannot respond substantively, but my hope is this contribution will encourage someone else to take up the challenge before I can return to it.

The first dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int subid byte(prgwk session_num attendance notattend) float(ynattend x2temp keep x3temp)
101  1 2 1 . 1 6  1 .
101  1 1 1 . 1 6  2 .
101  2 1 1 . 1 6  3 .
101  2 2 2 1 0 6  4 .
101  3 2 1 . 1 6  5 .
101  3 1 1 . 1 6  6 .
101  4 2 1 . 1 6  7 .
101  4 1 2 1 0 6  8 .
101  5 2 1 . 1 .  9 5
101  5 1 1 . 1 . 10 5
101  6 1 2 1 0 . 11 5
101  6 2 2 1 0 . 12 5
101  7 1 1 . 1 . 13 5
101  7 2 2 1 0 . 14 5
101  8 1 1 . 1 . 15 5
101  8 2 2 1 0 . 16 5
101  9 1 2 1 0 . 17 5
101  9 2 2 1 0 . 18 5
101 10 1 2 1 0 . 19 5
101 10 2 2 1 0 . 20 5
101 11 2 2 1 0 . 21 5
101 11 1 2 1 0 . 22 5
101 12 1 2 1 0 . 23 5
101 12 2 1 . 1 . 24 5
102  1 1 1 . 1 2  1 .
102  1 2 2 1 0 2  2 .
102  2 2 2 1 0 2  3 .
102  2 1 2 1 0 2  4 .
102  3 2 2 1 0 2  5 .
102  3 1 2 1 0 2  6 .
102  4 1 2 1 0 2  7 .
102  4 2 1 . 1 2  8 .
102  5 1 1 . 1 .  9 8
102  5 2 1 . 1 . 10 8
102  6 2 2 1 0 . 11 8
102  6 1 2 1 0 . 12 8
102  7 2 2 1 0 . 13 8
102  7 1 2 1 0 . 14 8
102  8 1 2 1 0 . 15 8
102  8 2 2 1 0 . 16 8
102  9 1 2 1 0 . 17 8
102  9 2 1 . 1 . 18 8
102 10 2 1 . 1 . 19 8
102 10 1 1 . 1 . 20 8
102 11 1 2 1 0 . 21 8
102 11 2 1 . 1 . 22 8
102 12 2 1 . 1 . 23 8
102 12 1 1 . 1 . 24 8
103  1 2 2 1 0 4  1 .
103  1 1 1 . 1 4  2 .
103  2 2 2 1 0 4  3 .
103  2 1 1 . 1 4  4 .
103  3 1 1 . 1 4  5 .
103  3 2 2 1 0 4  6 .
103  4 2 1 . 1 4  7 .
103  4 1 2 1 0 4  8 .
103  5 2 2 1 0 .  9 1
103  5 1 1 . 1 . 10 1
103  6 2 2 1 0 . 11 1
103  6 1 2 1 0 . 12 1
103  7 1 2 1 0 . 13 1
103  7 2 2 1 0 . 14 1
103  8 1 2 1 0 . 15 1
103  8 2 2 1 0 . 16 1
103  9 2 2 1 0 . 17 1
103  9 1 2 1 0 . 18 1
103 10 1 2 1 0 . 19 1
103 10 2 2 1 0 . 20 1
103 11 1 2 1 0 . 21 1
103 11 2 2 1 0 . 22 1
103 12 1 2 1 0 . 23 1
103 12 2 2 1 0 . 24 1
104  1 1 1 . 1 3  1 .
104  1 2 2 1 0 3  2 .
104  2 2 2 1 0 3  3 .
104  2 1 2 1 0 3  4 .
104  3 2 2 1 0 3  5 .
104  3 1 2 1 0 3  6 .
104  4 1 1 . 1 3  7 .
104  4 2 1 . 1 3  8 .
104  5 1 1 . 1 .  9 8
104  5 2 1 . 1 . 10 8
104  6 2 2 1 0 . 11 8
104  6 1 2 1 0 . 12 8
104  7 2 2 1 0 . 13 8
104  7 1 2 1 0 . 14 8
104  8 1 2 1 0 . 15 8
104  8 2 2 1 0 . 16 8
104  9 1 2 1 0 . 17 8
104  9 2 1 . 1 . 18 8
104 10 2 1 . 1 . 19 8
104 10 1 1 . 1 . 20 8
104 11 2 1 . 1 . 21 8
104 11 1 2 1 0 . 22 8
104 12 1 1 . 1 . 23 8
104 12 2 1 . 1 . 24 8
105  1 1 1 . 1 7  1 .
105  1 2 1 . 1 7  2 .
105  2 2 1 . 1 7  3 .
105  2 1 1 . 1 7  4 .
end

The second dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int subid byte(prgwk session_num attendance notattend) float(ynattend x2temp keep x3temp early_attend)
101 1 2 1 . 1 6 1  . .
101 5 2 1 . 1 . 9  5 .
102 1 1 1 . 1 2 1  . .
102 5 1 1 . 1 . 9  8 .
103 1 2 2 1 0 4 1  . .
103 5 2 2 1 0 . 9  1 .
104 1 1 1 . 1 3 1  . .
104 5 1 1 . 1 . 9  8 .
105 1 1 1 . 1 7 1  . .
105 5 1 2 1 0 . 9  6 .
106 1 2 1 . 1 8 1  . .
106 5 1 1 . 1 . 9 12 .
107 1 1 1 . 1 3 1  . .
107 5 2 2 1 0 . 9  1 .
108 1 2 1 . 1 7 1  . .
108 5 2 1 . 1 . 9 12 .
109 1 1 1 . 1 5 1  . .
109 5 1 2 1 0 . 9  8 .
110 1 1 1 . 1 3 1  . .
110 5 1 1 . 1 . 9 13 .
111 1 2 2 1 0 3 1  . .
111 5 2 1 . 1 . 9 13 .
112 1 2 1 . 1 5 1  . .
112 5 2 2 1 0 . 9  1 .
113 1 1 1 . 1 8 1  . .
113 5 2 1 . 1 . 9 14 .
114 1 1 1 . 1 6 1  . .
114 5 1 1 . 1 . 9 13 .
115 1 2 1 . 1 6 1  . .
115 5 1 1 . 1 . 9  7 .
116 1 1 1 . 1 6 1  . .
116 5 1 1 . 1 . 9  9 .
117 1 1 1 . 1 4 1  . .
117 5 2 2 1 0 . 9  1 .
118 1 1 1 . 1 8 1  . .
118 5 2 1 . 1 . 9 10 .
119 1 1 1 . 1 6 1  . .
119 5 1 1 . 1 . 9  7 .
120 1 2 1 . 1 7 1  . .
120 5 2 2 1 0 . 9 11 .
121 1 2 1 . 1 7 1  . .
121 5 1 1 . 1 . 9 13 .
122 1 1 1 . 1 5 1  . .
122 5 2 1 . 1 . 9  6 .
123 1 2 2 1 0 4 1  . .
123 5 1 1 . 1 . 9  2 .
124 1 2 2 1 0 6 1  . .
124 5 2 1 . 1 . 9 11 .
125 1 1 1 . 1 8 1  . .
125 5 1 1 . 1 . 9  7 .
126 1 1 1 . 1 4 1  . .
126 5 1 1 . 1 . 9 11 .
127 1 2 2 2 0 1 1  . .
127 5 1 2 2 0 . 9  0 .
128 1 1 1 . 1 7 1  . .
128 5 2 1 . 1 . 9  8 .
129 1 1 1 . 1 3 1  . .
129 5 1 1 . 1 . 9  7 .
130 1 1 1 . 1 7 1  . .
130 5 1 1 . 1 . 9  5 .
131 1 1 1 . 1 4 1  . .
131 5 2 1 . 1 . 9 14 .
132 1 2 1 . 1 4 1  . .
132 5 1 1 . 1 . 9 10 .
133 1 2 1 . 1 4 1  . .
133 5 2 2 1 0 . 9  0 .
134 1 1 1 . 1 4 1  . .
134 5 2 2 1 0 . 9  0 .
135 1 2 1 . 1 5 1  . .
135 5 2 2 1 0 . 9  2 .
136 1 1 1 . 1 5 1  . .
136 5 2 2 1 0 . 9  2 .
137 1 2 1 . 1 5 1  . .
137 5 2 1 . 1 . 9  6 .
138 1 1 1 . 1 6 1  . .
138 5 1 2 1 0 . 9  6 .
139 1 2 1 . 1 8 1  . .
139 5 2 2 1 0 . 9  8 .
140 1 1 2 1 0 6 1  . .
140 5 1 1 . 1 . 9 13 .
142 1 1 2 1 0 6 1  . .
142 5 2 2 1 0 . 9  7 .
143 1 1 2 1 0 2 1  . .
143 5 1 2 1 0 . 9  0 .
144 1 2 1 . 1 4 1  . .
144 5 1 2 1 0 . 9  0 .
145 1 2 2 1 0 1 1  . .
145 5 1 1 . 1 . 9  3 .
146 1 1 2 1 0 1 1  . .
146 5 1 1 . 1 . 9  3 .
147 1 1 2 1 0 2 1  . .
147 5 2 2 1 0 . 9  1 .
148 1 2 2 1 0 4 1  . .
148 5 1 2 1 0 . 9  4 .
149 1 2 2 1 0 5 1  . .
149 5 2 1 . 1 . 9  8 .
150 1 2 2 1 0 3 1  . .
150 5 1 2 1 0 . 9  2 .
151 1 1 2 1 0 6 1  . .
151 5 1 1 . 1 . 9 12 .
end

[William Lisowski]

Looking at your data, and your question, I am unclear on what you want.

Your sample data shows four subjects, 12 weeks per subject, and 2 sessions per week. You say

I wanted to create a variable (early attender: last variable) to describe those who attended 5 weeks and left attending.

The following questions may help me understand.

• The variable attendance is coded 1 or 2: is it the case that 1 means they did not attend and 2 means they attended?
• By "attended 5 weeks" do you mean you want them to have attended at least one session in each of the first 5 weeks? Or do you mean both sessions in each of the first 5 weeks? Or, I think I see now, do you mean "good attendance" of 8 sessions in the first 5 weeks?
• What does it mean for them to have "left attending"?

[Yuba Raj]

Hi William
The variable ynattend is same as attendance I replaced 2 "not attended" into "0' in "ynattend"
So 1 means attend and 0 means not attended in variable ynattend

How I wanted to determine early quitters was : those who attended at least 8 sessions out of (5weeks*2 seesions) and did not attend any of the sessions in later weeks (week 6-week12) then I wanted to qualify them as early quitters.

The first dataset shows only 5 people just for example but there are over 50 participants in total.
Thank you.

[William Lisowski]

Here are two approaches, depending on what you want. Both start with your sample data.

If you want to end with one observation per subjid:

generate n_1_5  = ynattend & prgwk<=5
generate n_6_12 = ynattend & prgwk>=6
collapse (sum) n_1_5 n_6_12, by(subid)
generate early_leaver = n_1_5>=8 & n_6_12==0
list, clean noobs abbreviate(12)

. list, clean noobs abbreviate(12)

    subid   n_1_5   n_6_12   early_leaver  
      101       8        3              0  
      102       4        6              0  
      103       5        0              0  
      104       5        6              0  
      105       4        0              0

If you don't want to reduce your data to a single observation per subject, the following will just add variables to your existing data.

bysort subid: egen n_1_5 = total(ynattend & prgwk<=5)
bysort subid: egen n_6_12 = total(ynattend & prgwk>=6)
generate early_leaver = n_1_5>=8 & n_6_12==0

. // just list one observation per subjid
. list subid n_1_5 n_6_12 early_leaver if prgwk==1 & session_num==1, clean noobs abbreviate(12)

    subid   n_1_5   n_6_12   early_leaver  
      101       8        3              0  
      102       4        6              0  
      103       5        0              0  
      104       5        6              0  
      105       4        0              0

The same approach can be used to generate your indicator of individuals who only attended late weeks.

[Yuba Raj]

Thank you William