Panel Data

[David Wambui]

I am working with panel data and want to tabulate new cases for antibody testing for the following month. So if an individual was positive last month and they are positive in the current month, I want to exclude them from the tabulation. So if I want to see new cases for say February 2021, I want to exclude those that were also positive in January 2021.

[David Wambui]

here is how my data look like. So I want to cross tab vax_status and abpositive for each month but I want to exclude abpositive that are a "yes" for the current month and were a "yes" in the previous month.

I appreciate any ideas

[Clyde Schechter]

The data shown in the screenshot looks like it is the result of inappropriately combining a data set in long layout with another in wide layout, resulting in a hybrid that is going to be very difficult to work with. It requires some "surgery" to turn it into a usable data set. Once that is done, solving the problem you posed is actually pretty simple. But there are enough steps altogether, that I am reluctant to offer you code that I cannot run and test on example data-I don't want to waste your time with code that will require (possibly several) modifications in order to actually work with your data. As the Forum FAQ makes clear (#12 in particular) screenshots are a particularly unhelpful way to provide example data. Please post back with a usable example data set by using the -dataex- command. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to 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-.

[David Wambui]

Thanks Clyde. You are very right, my data is in long format and this is my first time working with long format data. I I'm trying to create these dummy variables in wide to be able to work with. I am also new in the forum and I didn't know the best way to present my data here. I have followed your advise and below is a sample of the data that I am working with. I appreciate your feedback

* Example generated by -dataex-. For more info, type help dataex
clear
input int unique_id float(study_date vax_status_dec20 vax_status_jan21 vax_status_feb21 abpositive_dec20 abpositive_jan21 abpositive_feb21)
1001     . . . . . . .
1002 22226 3 3 3 . . .
1002 22411 3 3 3 . . .
1002 22254 3 3 3 0 . .
1002 22660 3 3 3 . . .
1002 22198 3 3 3 . . .
1002 22467 3 3 3 . . .
1002 22574 3 3 3 . . .
1002 22630 3 3 3 . . .
1002 22394 3 3 3 . . .
1002 22338 3 3 3 . . 0
1002     . 3 3 3 . . .
1002 22242 3 3 3 . . .
1002     . 3 3 3 . . .
1002     . 3 3 3 . . .
1002 22587 3 3 3 . . .
1002 22354 3 3 3 . . .
1002 22282 3 3 3 . 0 .
1002 22366 3 3 3 . . .
1002 22215 3 3 3 . . .
1002 22439 3 3 3 . . .
1002     . 3 3 3 . . .
1002     . 3 3 3 . . .
1002     . 3 3 3 . . .
1002 22613 3 3 3 . . .
1002 22170 3 3 3 . . .
1002 22481 3 3 3 . . .
1002 22452 3 3 3 . . .
1002 22382 3 3 3 . . .
1002 22328 3 3 3 . . .
1002 22310 3 3 3 . 0 .
1002     . 3 3 3 . . .
1002     . 3 3 3 . . .
1002     . 3 3 3 . . .
1002 22604 3 3 3 . . .
1002 22298 3 3 3 . . .
1002 22266 3 3 3 . . .
1003 22297 3 3 3 . . .
1003 22226 3 3 3 . . .
1003     . 3 3 3 . . .
1003 22437 3 3 3 . . .
1003 22269 3 3 3 . . .
1003     . 3 3 3 . . .
1003 22632 3 3 3 . . .
1003     . 3 3 3 . . .
1003 22328 3 3 3 . . .
1003 22482 3 3 3 . . .
1003 22383 3 3 3 . . .
1003 22614 3 3 3 . . .
1003 22425 3 3 3 . . .
1003     . 3 3 3 . . .
1003 22366 3 3 3 . . .
1003 22467 3 3 3 . . .
1003 22587 3 3 3 . . .
1003 22604 3 3 3 . . .
1003 22394 3 3 3 . . .
1003     . 3 3 3 . . .
1003     . 3 3 3 . . .
1003 22454 3 3 3 . . .
1003 22215 3 3 3 . . .
1003 22356 3 3 3 . . .
1003 22576 3 3 3 . . .
1003 22170 3 3 3 . . .
1003 22660 3 3 3 . . .
1003 22310 3 3 3 . 0 .
1003     . 3 3 3 . . .
1003 22198 3 3 3 . . .
1003 22282 3 3 3 . 0 .
1003 22244 3 3 3 . . .
1003 22412 3 3 3 . . .
1003 22254 3 3 3 0 . .
1003     . 3 3 3 . . .
1003 22338 3 3 3 . . 0
1004 22356 2 3 1 . . .
1004     . 2 3 1 . . .
1004 22254 2 3 1 0 . .
1004     . 2 3 1 . . .
1004 22282 2 3 1 . 0 .
1004 22384 2 3 1 . . .
1004     . 2 3 1 . . .
1004 22482 2 3 1 . . .
1004     . 2 3 1 . . .
1004 22328 2 3 1 . . .
1004 22394 2 3 1 . . .
1004 22573 2 3 1 . . .
1004 22412 2 3 1 . . .
1004 22587 2 3 1 . . .
1004 22467 2 3 1 . . .
1004     . 2 3 1 . . .
1004 22453 2 3 1 . . .
1004 22426 2 3 1 . . .
1004     . 2 3 1 . . .
1004 22198 2 3 1 . . .
1004     . 2 3 1 . . .
1004 22660 . . . . . .
1004 22440 2 3 1 . . .
1004     . 2 3 1 . . .
1004 22604 2 3 1 . . .
1004 22299 2 3 1 . . .
1004     . 2 3 1 . . .
end
format %dM_d,_CY study_date
label values vax_status_dec20 vaxstatus
label values vax_status_jan21 vaxstatus
label values vax_status_feb21 vaxstatus
label def vaxstatus 2 "Partially Vaccinated", modify
label def vaxstatus 3 "Unvaccinated", modify
label def vaxstatus 1 "Fully Vaccinated", modify
label values abpositive_dec20 yesno
label values abpositive_jan21 yesno
label values abpositive_feb21 yesno
label def yesno 0 "No", modify
label var unique_id "Unique Study ID" 
label var study_date "Date of study visit"

[David Wambui]

Here is a better sample data that I'm using. I have included the variable ab_result that I used to generate the dummy monthly ab_results (abpositive_dec20, abpositive_jan21 etc). If there's a way I can work with the long format to get the same results, I'd appreciate it. I'm just not used to long format data.

* Example generated by -dataex-. For more info, type help dataex
clear
input int unique_id float(study_date vax_status_dec20 vax_status_jan21 vax_status_feb21) byte ab_result float(abpositive_dec20 abpositive_jan21 abpositive_feb21)
1001     . . . . . . . .
1002 22226 3 3 3 0 . . .
1002 22411 3 3 3 . . . .
1002 22254 3 3 3 0 0 . .
1002 22660 3 3 3 . . . .
1002 22198 3 3 3 0 . . .
1002 22467 3 3 3 . . . .
1002 22574 3 3 3 . . . .
1002 22630 3 3 3 . . . .
1002 22394 3 3 3 0 . . .
1002 22338 3 3 3 0 . . 0
1002     . 3 3 3 . . . .
1002 22242 3 3 3 . . . .
1002     . 3 3 3 . . . .
1002     . 3 3 3 . . . .
1002 22587 3 3 3 . . . .
1002 22354 3 3 3 . . . .
1002 22282 3 3 3 0 . 0 .
1002 22366 3 3 3 0 . . .
1002 22215 3 3 3 . . . .
1002 22439 3 3 3 . . . .
1002     . 3 3 3 . . . .
1002     . 3 3 3 . . . .
1002     . 3 3 3 . . . .
1002 22613 3 3 3 . . . .
1002 22170 3 3 3 0 . . .
1002 22481 3 3 3 . . . .
1002 22452 3 3 3 . . . .
1002 22382 3 3 3 . . . .
1002 22328 3 3 3 . . . .
1002 22310 3 3 3 0 . 0 .
1002     . 3 3 3 . . . .
1002     . 3 3 3 . . . .
1002     . 3 3 3 . . . .
1002 22604 3 3 3 . . . .
1002 22298 3 3 3 . . . .
1002 22266 3 3 3 . . . .
1003 22297 3 3 3 . . . .
1003 22226 3 3 3 0 . . .
1003     . 3 3 3 . . . .
1003 22437 3 3 3 . . . .
1003 22269 3 3 3 . . . .
1003     . 3 3 3 . . . .
1003 22632 3 3 3 . . . .
1003     . 3 3 3 . . . .
1003 22328 3 3 3 . . . .
1003 22482 3 3 3 . . . .
1003 22383 3 3 3 . . . .
1003 22614 3 3 3 . . . .
1003 22425 3 3 3 . . . .
1003     . 3 3 3 . . . .
1003 22366 3 3 3 0 . . .
1003 22467 3 3 3 . . . .
1003 22587 3 3 3 . . . .
1003 22604 3 3 3 . . . .
1003 22394 3 3 3 0 . . .
1003     . 3 3 3 . . . .
1003     . 3 3 3 . . . .
1003 22454 3 3 3 . . . .
1003 22215 3 3 3 . . . .
1003 22356 3 3 3 . . . .
1003 22576 3 3 3 . . . .
1003 22170 3 3 3 0 . . .
1003 22660 3 3 3 . . . .
1003 22310 3 3 3 0 . 0 .
1003     . 3 3 3 . . . .
1003 22198 3 3 3 0 . . .
1003 22282 3 3 3 0 . 0 .
1003 22244 3 3 3 . . . .
1003 22412 3 3 3 . . . .
1003 22254 3 3 3 0 0 . .
1003     . 3 3 3 . . . .
1003 22338 3 3 3 0 . . 0
1004 22356 2 3 1 . . . .
1004     . 2 3 1 . . . .
1004 22254 2 3 1 0 0 . .
1004     . 2 3 1 . . . .
1004 22282 2 3 1 0 . 0 .
1004 22384 2 3 1 . . . .
1004     . 2 3 1 . . . .
1004 22482 2 3 1 . . . .
1004     . 2 3 1 . . . .
1004 22328 2 3 1 . . . .
1004 22394 2 3 1 0 . . .
1004 22573 2 3 1 . . . .
1004 22412 2 3 1 . . . .
1004 22587 2 3 1 . . . .
1004 22467 2 3 1 . . . .
1004     . 2 3 1 . . . .
1004 22453 2 3 1 . . . .
1004 22426 2 3 1 . . . .
1004     . 2 3 1 . . . .
1004 22198 2 3 1 0 . . .
1004     . 2 3 1 . . . .
1004 22660 . . . . . . .
1004 22440 2 3 1 . . . .
1004     . 2 3 1 . . . .
1004 22604 2 3 1 . . . .
1004 22299 2 3 1 . . . .
1004     . 2 3 1 . . . .
1004 22242 2 3 1 . . . .
1004 22170 2 3 1 0 . . .
1004     . 2 3 1 . . . .
1004 22226 2 3 1 0 . . .
1004     . 2 3 1 . . . .
1004 22338 2 3 1 0 . . 0
1004 22267 2 3 1 . . . .
1004 22310 2 3 1 0 . 0 .
1004 22366 2 3 1 0 . . .
1004 22631 2 3 1 . . . .
1005     . . . . . . . .
1006     . 3 3 3 . . . .
1006 22170 3 3 3 0 . . .
1006     . 3 3 3 . . . .
1006 22241 3 3 3 . . . .
1006 22214 3 3 3 . . . .
1006 22617 3 3 3 . . . .
1006 22226 3 3 3 0 . . .
1006     . 3 3 3 . . . .
1006     . 3 3 3 . . . .
1006 22652 3 3 3 . . . .
1006 22328 3 3 3 . . . .
1006     . 3 3 3 . . . .
1006 22267 3 3 3 . . . .
1006 22366 3 3 3 0 . . .
1006     . 3 3 3 . . . .
1006 22338 3 3 3 0 . . 0
1006 22394 3 3 3 . . . .
1006 22310 3 3 3 0 . 0 .
1006 22282 3 3 3 0 . 0 .
1006 22601 3 3 3 . . . .
1006 22576 3 3 3 . . . .
1006 22424 3 3 3 . . . .
1006 22254 3 3 3 0 0 . .
1006 22589 3 3 3 . . . .
1006 22198 3 3 3 0 . . .
1006     . 3 3 3 . . . .
1006 22356 3 3 3 . . . .
1006     . 3 3 3 . . . .
1006 22475 3 3 3 . . . .
1006     . 3 3 3 . . . .
1006     . 3 3 3 . . . .
1006     . 3 3 3 . . . .
1006     . 3 3 3 . . . .
1007     . . . . . . . .
1008 22267 3 2 1 . . . .
1008 22366 3 2 1 0 . . .
1008 22242 3 2 1 . . . .
1008 22351 3 2 1 . . . .
1008 22226 3 2 1 0 . . .
1008 22170 3 2 1 0 . . .
1008 22338 3 2 1 0 . . 0
1008 22198 3 2 1 0 . . .
1008 22254 3 2 1 0 0 . .
1008 22282 3 2 1 0 . 0 .
1008 22384 3 2 1 . . . .
1008     . 3 2 1 . . . .
1008     . 3 2 1 . . . .
1008 22394 3 2 1 . . . .
1008 22310 3 2 1 0 . 0 .
1008     . 3 2 1 . . . .
1008 22214 3 2 1 . . . .
1009     . . . . . . . .
1010 22243 3 2 2 . . . .
1010 22282 3 2 2 0 . 0 .
1010     . 3 2 2 . . . .
1010 22338 3 2 2 0 . . 0
1010 22266 3 2 2 . . . .
1010     . 3 2 2 . . . .
1010 22226 3 2 2 0 . . .
1010     . 3 2 2 . . . .
1010 22445 3 2 2 . . . .
1010 22380 3 2 2 0 . . .
1010 22298 3 2 2 . . . .
1010 22310 3 2 2 0 . 0 .
1010 22198 3 2 2 0 . . .
1010 22585 3 2 2 . . . .
1010 22604 3 2 2 . . . .
1010     . 3 2 2 . . . .
1010     . 3 2 2 . . . .
1010 22410 3 2 2 . . . .
1010 22632 3 2 2 . . . .
1010 22354 3 2 2 . . . .
1010 22614 3 2 2 . . . .
1010 22474 3 2 2 . . . .
1010 22366 3 2 2 . . . .
1010 22487 3 2 2 . . . .
1010     . 3 2 2 . . . .
1010 22326 3 2 2 . . . .
1010     . 3 2 2 . . . .
1010     . 3 2 2 . . . .
1010 22254 3 2 2 0 0 . .
1010 22576 3 2 2 . . . .
1010 22460 3 2 2 . . . .
1010 22660 3 2 2 . . . .
1010 22170 3 2 2 0 . . .
1010 22214 3 2 2 . . . .
1010 22395 3 2 2 . . . .
1011     . . . . . . . .
1012     . . . . . . . .
1013 22314 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013 22398 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013 22228 3 3 2 . . . .
1013 22658 3 3 2 . . . .
1013 22184 3 3 2 0 . . .
1013     . 3 3 2 . . . .
1013 22575 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013 22380 3 3 2 0 . . .
1013     . 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013 22256 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013 22352 3 3 2 0 . . .
1013 22603 3 3 2 . . . .
1013 22296 3 3 2 0 . 0 .
1013 22212 3 3 2 0 . . .
1013 22630 3 3 2 . . . .
1013     . 3 3 2 . . . .
1013 22324 3 3 2 0 . . 0
1013 22240 3 3 2 0 . . .
1013 22268 3 3 2 0 0 . .
1014     . 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014 22300 3 3 3 . . . .
1014 22294 3 3 3 . . . .
1014 22617 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014 22198 3 3 3 0 . . .
1014 22243 3 3 3 . . . .
1014 22327 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014 22355 3 3 3 . . . .
1014 22254 3 3 3 0 0 . .
1014     . 3 3 3 . . . .
1014 22269 3 3 3 . . . .
1014 22170 3 3 3 0 . . .
1014 22338 3 3 3 0 . . 0
1014 22384 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014 22601 3 3 3 . . . .
1014 22394 3 3 3 0 . . .
1014 22575 3 3 3 . . . .
1014 22366 3 3 3 0 . . .
1014 22226 3 3 3 0 . . .
1014 22282 3 3 3 0 . 0 .
1014     . 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014 22215 3 3 3 . . . .
1014 22589 3 3 3 . . . .
1014     . 3 3 3 . . . .
1014 22310 3 3 3 0 . 0 .
1015     . 3 2 2 . . . .
1015 22453 3 2 2 . . . .
1015 22214 3 2 2 . . . .
1015 22383 3 2 2 . . . .
1015 22411 3 2 2 . . . .
1015 22480 3 2 2 . . . .
1015 22366 3 2 2 0 . . .
1015 22338 3 2 2 0 . . 0
1015     . 3 2 2 . . . .
1015     . 3 2 2 . . . .
1015     . 3 2 2 . . . .
1015 22351 3 2 2 . . . .
1015     . 3 2 2 . . . .
1015     . 3 2 2 . . . .
1015 22270 3 2 2 . . . .
1015 22426 3 2 2 . . . .
1015 22660 3 2 2 . . . .
1015 22310 3 2 2 0 . 0 .
1015 22439 3 2 2 . . . .
1015 22226 3 2 2 0 . . .
1015 22244 3 2 2 . . . .
1015 22604 3 2 2 . . . .
1015     . 3 2 2 . . . .
1015 22632 3 2 2 . . . .
1015 22282 3 2 2 0 . 0 .
1015 22198 3 2 2 0 . . .
1015 22614 3 2 2 . . . .
1015 22170 3 2 2 0 . . .
1015 22467 3 2 2 . . . .
1015 22254 3 2 2 0 0 . .
1015 22394 3 2 2 0 . . .
1015 22351 3 2 2 . . . .
1015 22576 3 2 2 . . . .
1015     . 3 2 2 . . . .
1015     . 3 2 2 . . . .
1015 22327 3 2 2 . . . .
1016     . . . . . . . .
1017 22384 3 3 3 . . . .
1017 22632 3 3 3 . . . .
1017 22604 3 3 3 . . . .
1017     . 3 3 3 . . . .
1017 22198 3 3 3 0 . . .
1017     . 3 3 3 . . . .
1017 22576 3 3 3 . . . .
1017 22282 3 3 3 5 . . .
1017     . 3 3 3 . . . .
1017 22170 3 3 3 0 . . .
1017 22587 3 3 3 . . . .
1017 22615 3 3 3 . . . .
1017 22254 3 3 3 0 0 . .
1017     . 3 3 3 . . . .
1017 22226 3 3 3 0 . . .
1017 22424 3 3 3 . . . .
1017 22270 3 3 3 . . . .
1017 22215 3 3 3 . . . .
1017 22338 3 3 3 0 . . 0
1017     . 3 3 3 . . . .
1017 22451 3 3 3 . . . .
1017 22466 3 3 3 . . . .
1017 22366 3 3 3 0 . . .
1017     . 3 3 3 . . . .
1017 22479 3 3 3 . . . .
1017 22394 3 3 3 0 . . .
1017     . 3 3 3 . . . .
1017 22310 3 3 3 0 . 0 .
1017 22351 3 3 3 . . . .
1017 22299 3 3 3 . . . .
1017 22326 3 3 3 . . . .
1017 22408 3 3 3 . . . .
1017     . 3 3 3 . . . .
1017 22242 3 3 3 . . . .
1017 22437 3 3 3 . . . .
1017 22660 3 3 3 . . . .
1018     . . . . . . . .
1019 22282 3 2 2 0 . 0 .
1019 22216 3 2 2 . . . .
1019     . 3 2 2 . . . .
1019     . 3 2 2 . . . .
1019     . 3 2 2 . . . .
1019 22301 3 2 2 . . . .
1019 22226 3 2 2 0 . . .
1019 22170 3 2 2 0 . . .
1019     . 3 2 2 . . . .
1019 22394 3 2 2 0 . . .
1019 22310 3 2 2 0 . 0 .
1019 22198 3 2 2 0 . . .
1019 22243 3 2 2 . . . .
1019 22269 3 2 2 . . . .
1019 22356 3 2 2 . . . .
1019 22366 3 2 2 0 . . .
1019 22338 3 2 2 0 . . 0
1019     . 3 2 2 . . . .
1019     . 3 2 2 . . . .
1019 22326 3 2 2 . . . .
1019 22254 3 2 2 0 0 . .
1020     . . . . . . . .
1021     . . . . . . . .
1022     . . . . . . . .
1023     . 3 2 2 . . . .
1023 22356 3 2 2 . . . .
1023 22170 3 2 2 1 . . .
1023 22226 3 2 2 1 . . .
1023 22254 3 2 2 1 1 . .
1023     . 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023 22481 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023 22576 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023 22310 3 2 2 1 . 1 .
1023 22282 3 2 2 1 . 1 .
1023 22586 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023 22328 3 2 2 . . . .
1023 22660 3 2 2 . . . .
1023 22299 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023 22604 3 2 2 . . . .
1023 22426 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023 22450 3 2 2 . . . .
1023 22198 3 2 2 1 . . .
1023 22338 3 2 2 1 . . 1
1023 22439 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023 22213 3 2 2 . . . .
1023 22384 3 2 2 . . . .
1023 22366 3 2 2 1 . . .
1023 22632 3 2 2 . . . .
1023 22268 3 2 2 . . . .
1023     . 3 2 2 . . . .
1023 22394 3 2 2 0 . . .
1024     . 3 3 2 . . . .
1024 22576 3 3 2 . . . .
1024 22397 3 3 2 . . . .
1024 22445 3 3 2 . . . .
1024 22324 3 3 2 0 . . 0
1024 22240 3 3 2 0 . . .
1024     . 3 3 2 . . . .
1024 22632 3 3 2 . . . .
1024 22340 3 3 2 . . . .
1024     . 3 3 2 . . . .
1024 22459 3 3 2 . . . .
1024 22184 3 3 2 0 . . .
1024     . 3 3 2 . . . .
1024 22424 3 3 2 . . . .
1024 22613 3 3 2 . . . .
1024 22314 3 3 2 . . . .
1024 22410 3 3 2 . . . .
1024     . 3 3 2 . . . .
1024     . 3 3 2 . . . .
1024 22212 3 3 2 0 . . .
1024 22368 3 3 2 . . . .
1024 22380 3 3 2 0 . . .
1024     . 3 3 2 . . . .
1024 22268 3 3 2 0 0 . .
1024 22660 3 3 2 . . . .
1024     . 3 3 2 . . . .
1024 22352 3 3 2 0 . . .
1024 22488 3 3 2 . . . .
1024 22296 3 3 2 0 . 0 .
1024 22586 3 3 2 . . . .
1024 22604 3 3 2 . . . .
1024 22256 3 3 2 . . . .
1024 22228 3 3 2 . . . .
1024 22473 3 3 2 . . . .
1025     . . . . . . . .
1026     . . . . . . . .
1027     . . . . . . . .
1028     . . . . . . . .
1029 22475 3 3 3 . . . .
1029 22394 3 3 3 0 . . .
1029 22562 3 3 3 . . . .
1029 22366 3 3 3 0 . . .
1029     . 3 3 3 . . . .
1029 22241 3 3 3 . . . .
1029 22198 3 3 3 0 . . .
1029 22301 3 3 3 . . . .
1029 22282 3 3 3 0 . 0 .
1029 22572 3 3 3 . . . .
1029     . 3 3 3 . . . .
1029 22226 3 3 3 0 . . .
1029 22652 3 3 3 . . . .
1029 22328 3 3 3 . . . .
1029 22310 3 3 3 0 . 0 .
1029 22411 3 3 3 . . . .
1029     . 3 3 3 . . . .
1029 22589 3 3 3 . . . .
1029 22445 3 3 3 . . . .
1029 22338 3 3 3 0 . . 0
1029 22461 3 3 3 . . . .
1029     . 3 3 3 . . . .
1029 22354 3 3 3 . . . .
1029 22617 3 3 3 . . . .
1029 22254 3 3 3 . . . .
1029     . 3 3 3 . . . .
1029     . 3 3 3 . . . .
1029 22384 3 3 3 . . . .
1029 22424 3 3 3 . . . .
1029 22268 3 3 3 0 0 . .
1029 22601 3 3 3 . . . .
1029     . 3 3 3 . . . .
1029 22487 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030 22454 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030 22394 3 3 3 0 . . .
1030     . 3 3 3 . . . .
1030 22170 3 3 3 0 . . .
1030 22575 3 3 3 . . . .
1030 22366 3 3 3 0 . . .
1030     . 3 3 3 . . . .
1030 22226 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030 22338 3 3 3 0 . . 0
1030 22268 3 3 3 0 0 . .
1030     . 3 3 3 . . . .
1030 22310 3 3 3 0 . 0 .
1030     . 3 3 3 . . . .
1030 22326 3 3 3 . . . .
1030 22651 3 3 3 . . . .
1030 22254 3 3 3 . . . .
1030 22589 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030 22198 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030 22357 3 3 3 . . . .
1030 22617 3 3 3 . . . .
1030     . 3 3 3 . . . .
1030 22282 3 3 3 0 . 0 .
1030     . 3 3 3 . . . .
1030 22296 3 3 3 . . . .
1031     . . . . . . . .
1031 22394 . . . . . . .
1031     . . . . . . . .
1031     . . . . . . . .
1031 22243 . . . . . . .
1031 22230 . . . . . . .
1031 22324 . . . 0 . . 0
1031 22319 . . . . . . .
1031 22212 . . . 0 . . .
1031     . . . . . . . .
1031 22184 . . . 0 . . .
1031 22345 . . . . . . .
1031 22296 . . . 0 . 0 .
1031 22352 . . . . . . .
1032     . . . . . . . .
1033     . . . . . . . .
1034     . 3 3 3 . . . .
1034 22244 3 3 3 . . . .
1034 22356 3 3 3 . . . .
1034 22324 3 3 3 0 . . 0
1034 22215 3 3 3 . . . .
1034     . 3 3 3 . . . .
1034 22614 3 3 3 . . . .
1034     . 3 3 3 . . . .
1034 22576 3 3 3 . . . .
1034 22226 3 3 3 0 . . .
1034     . 3 3 3 . . . .
1034     . 3 3 3 . . . .
1034     . 3 3 3 . . . .
1034     . 3 3 3 . . . .
1034 22394 3 3 3 0 . . .
1034     . 3 3 3 . . . .
1034 22437 3 3 3 . . . .
1034 22338 3 3 3 0 . . 0
1034 22366 3 3 3 0 . . .
1034     . 3 3 3 . . . .
1034 22310 3 3 3 5 . . .
1034 22468 3 3 3 . . . .
1034 22383 3 3 3 . . . .
1034 22296 3 3 3 . . . .
1034 22198 3 3 3 0 . . .
1034 22604 3 3 3 . . . .
1034 22585 3 3 3 . . . .
1034     . 3 3 3 . . . .
1034     . 3 3 3 . . . .
1034 22660 3 3 3 . . . .
1034 22264 3 3 3 . . . .
1034 22170 3 3 3 0 . . .
1034 22254 3 3 3 0 0 . .
1034 22282 3 3 3 0 . 0 .
1034 22410 3 3 3 . . . .
1034 22632 3 3 3 . . . .
1035     . . . . . . . .
1036     . . . . . . . .
1037     . . . . . . . .
1038     . . . . . . . .
1039 22268 3 3 3 0 0 . .
1039 22296 3 3 3 . . . .
1039 22313 3 3 3 . . . .
1039     . 3 3 3 . . . .
1039     . 3 3 3 . . . .
1039 22240 3 3 3 0 . . .
1039     . 3 3 3 . . . .
1039 22352 3 3 3 . . . .
1039 22394 3 3 3 0 . . .
1039     . 3 3 3 . . . .
1039     . 3 3 3 . . . .
1039     . 3 3 3 . . . .
1039 22325 3 3 3 . . . .
1039     . 3 3 3 . . . .
1040     . . . . . . . .
1041     . . . . . . . .
1042     . . . . . . . .
1043     . . . . . . . .
1044     . . . . . . . .
1045     . . . . . . . .
1046     . . . . . . . .
1047     . . . . . . . .
1048     . . . . . . . .
1049 22170 . . . . . . .
1050     . . . . . . . .
1051     . . . . . . . .
1052 22282 . . . 0 . 0 .
1052     . . . . . . . .
1052 22296 . . . 0 . 0 .
1052 22352 . . . 0 . . .
1052 22380 . . . 0 . . .
1052 22240 . . . . . . .
1052     . . . . . . . .
1052     . . . . . . . .
1052     . . . . . . . .
1052 22268 . . . . . . .
1052 22184 . . . 0 . . .
1052     . . . . . . . .
1052     . . . . . . . .
1052 22254 . . . 0 0 . .
1052 22338 . . . . . . .
1053     . . . . . . . .
1054     . . . . . . . .
1055 22560 3 3 2 . . . .
1055 22459 3 3 2 . . . .
1055 22445 3 3 2 . . . .
1055     . 3 3 2 . . . .
1055 22410 3 3 2 . . . .
1055 22215 3 3 2 . . . .
1055 22299 3 3 2 . . . .
1055     . 3 3 2 . . . .
1055 22254 3 3 2 0 0 . .
1055 22326 3 3 2 . . . .
1055 22380 3 3 2 0 . . .
1055 22226 3 3 2 0 . . .
1055     . 3 3 2 . . . .
1055 22473 3 3 2 . . . .
1055     . 3 3 2 . . . .
1055 22355 3 3 2 . . . .
1055 22571 3 3 2 . . . .
1055 22242 3 3 2 . . . .
1055 22651 3 3 2 . . . .
1055 22338 3 3 2 0 . . 0
1055     . 3 3 2 . . . .
1055 22424 3 3 2 . . . .
1055 22486 3 3 2 . . . .
1055 22588 3 3 2 . . . .
1055 22601 3 3 2 . . . .
1055 22265 3 3 2 . . . .
1055     . 3 3 2 . . . .
1055     . 3 3 2 . . . .
1055 22366 3 3 2 5 . . .
1055 22282 3 3 2 0 . 0 .
1055 22198 3 3 2 0 . . .
1055 22310 3 3 2 0 . 0 .
1055 22396 3 3 2 . . . .
1055 22616 3 3 2 . . . .
1055 22170 3 3 2 0 . . .
1056     . . . . . . . .
1057     . . . . . . . .
1058     . . . . . . . .
1059     . . . . . . . .
1060 22338 . . . . . . .
1060 22215 . . . . . . .
1060 22324 . . . . . . .
1060 22282 . . . 0 . 0 .
1060 22270 . . . . . . .
1060     . . . . . . . .
1060 22198 . . . 0 . . .
1060 22226 . . . 0 . . .
1060 22170 . . . 0 . . .
1060     . . . . . . . .
1060 22254 . . . 0 0 . .
1060 22310 . . . . . . .
1060 22244 . . . . . . .
1061     . . . . . . . .
1061 22184 . . . 0 . . .
1061     . . . . . . . .
1061 22212 . . . . . . .
1061 22254 . . . . . . .
1061     . . . . . . . .
1062 22443 3 3 3 . . . .
1062     . 3 3 3 . . . .
1062 22313 3 3 3 . . . .
1062 22397 3 3 3 . . . .
1062     . 3 3 3 . . . .
1062 22212 3 3 3 0 . . .
1062     . 3 3 3 . . . .
1062 22474 3 3 3 . . . .
1062     . 3 3 3 . . . .
1062 22352 3 3 3 0 . . .
1062 22380 3 3 3 0 . . .
1062 22660 3 3 3 . . . .
1062 22586 3 3 3 . . . .
1062 22632 3 3 3 . . . .
1062 22622 3 3 3 . . . .
1062 22370 3 3 3 . . . .
1062 22240 3 3 3 0 . . .
1062     . 3 3 3 . . . .
1062 22459 3 3 3 . . . .
1062     . 3 3 3 . . . .
1062 22341 3 3 3 . . . .
1062 22602 3 3 3 . . . .
1062     . 3 3 3 . . . .
1062 22184 3 3 3 0 . . .
1062 22488 3 3 3 . . . .
1062     . 3 3 3 . . . .
1062 22614 3 3 3 . . . .
1062 22268 3 3 3 0 0 . .
1062 22227 3 3 3 . . . .
1062 22576 3 3 3 . . . .
1062 22424 3 3 3 . . . .
1062     . 3 3 3 . . . .
1062 22324 3 3 3 0 . . 0
1062 22296 3 3 3 0 . 0 .
1063     . . . . . . . .
1064     . . . . . . . .
1065     . 3 2 2 . . . .
1065 22352 3 2 2 0 . . .
1065 22456 3 2 2 . . . .
1065 22324 3 2 2 0 . . 0
1065     . 3 2 2 . . . .
1065     . 3 2 2 . . . .
1065     . 3 2 2 . . . .
1065     . 3 2 2 . . . .
1065     . 3 2 2 . . . .
1065     . 3 2 2 . . . .
1065 22574 3 2 2 . . . .
1065 22426 3 2 2 . . . .
1065 22412 3 2 2 . . . .
1065 22296 3 2 2 0 . 0 .
1065 22394 3 2 2 0 . . .
1065 22320 3 2 2 . . . .
1065 22659 3 2 2 . . . .
1065 22482 3 2 2 . . . .
1065     . 3 2 2 . . . .
1065     . 3 2 2 . . . .
1065 22268 3 2 2 0 0 . .
1065 22341 3 2 2 . . . .
1065 22602 3 2 2 . . . .
1065 22369 3 2 2 . . . .
1065     . 3 2 2 . . . .
1065 22631 3 2 2 . . . .
1065 22468 3 2 2 . . . .
1066 22226 . . . . . . .
1066 22310 . . . 0 . 0 .
1066     . . . . . . . .
1066 22184 . . . 0 . . .
1066     . . . . . . . .
1066 22338 . . . . . . .
1066     . . . . . . . .
1067     . . . . . . . .
1068 22170 . . . . . . .
1069     . . . . . . . .
1070     . . . . . . . .
1071 22254 . . . . . . .
1072     . . . . . . . .
1073 22310 3 3 3 0 . 0 .
1073 22265 3 3 3 . . . .
1073 22394 3 3 3 0 . . .
1073 22366 3 3 3 0 . . .
1073 22243 3 3 3 . . . .
1073 22226 3 3 3 0 . . .
1073 22327 3 3 3 . . . .
1073 22198 3 3 3 0 . . .
1073 22216 3 3 3 . . . .
1073 22170 3 3 3 0 . . .
1073 22356 3 3 3 . . . .
1073 22300 3 3 3 . . . .
1073 22282 3 3 3 0 . 0 .
1073     . 3 3 3 . . . .
1073 22254 3 3 3 0 0 . .
1073 22384 3 3 3 . . . .
1073 22338 3 3 3 0 . . 0
1074 22366 3 2 1 0 . . .
1074 22398 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074 22562 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074 22424 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074 22383 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074 22352 3 2 1 . . . .
1074 22310 3 2 1 0 . 0 .
1074 22254 3 2 1 0 0 . .
1074 22590 3 2 1 . . . .
1074 22328 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074 22282 3 2 1 0 . 0 .
1074     . 3 2 1 . . . .
1074     . 3 2 1 . . . .
1074 22473 3 2 1 . . . .
1075 22481 3 3 3 . . . .
1075     . 3 3 3 . . . .
1075     . 3 3 3 . . . .
1075 22384 3 3 3 . . . .
1075 22282 3 3 3 0 . 0 .
1075 22614 3 3 3 . . . .
1075 22338 3 3 3 0 . . 0
1075 22630 3 3 3 . . . .
1075 22254 3 3 3 0 0 . .
1075 22356 3 3 3 . . . .
1075     . 3 3 3 . . . .
1075     . 3 3 3 . . . .
1075 22574 3 3 3 . . . .
1075 22658 3 3 3 . . . .
1075 22226 3 3 3 0 . . .
1075 22366 3 3 3 0 . . .
1075     . 3 3 3 . . . .
1075 22394 3 3 3 0 . . .
1075 22267 3 3 3 . . . .
1075 22310 3 3 3 0 . 0 .
1075     . 3 3 3 . . . .
1075     . 3 3 3 . . . .
1075 22467 3 3 3 . . . .
1075     . 3 3 3 . . . .
1075     . 3 3 3 . . . .
1075 22454 3 3 3 . . . .
1075 22586 3 3 3 . . . .
1075 22411 3 3 3 . . . .
1075 22602 3 3 3 . . . .
1075 22299 3 3 3 . . . .
1075 22440 3 3 3 . . . .
1075 22328 3 3 3 . . . .
1076     . . . . . . . .
1077     . . . . . . . .
1078     . . . . . . . .
1079 22338 3 3 2 0 . . 0
1079 22198 3 3 2 0 . . .
1079     . 3 3 2 . . . .
1079 22560 3 3 2 . . . .
1079 22616 3 3 2 . . . .
1079 22254 3 3 2 0 0 . .
1079     . 3 3 2 . . . .
1079 22380 3 3 2 0 . . .
1079 22473 3 3 2 . . . .
1079     . 3 3 2 . . . .
1079 22226 3 3 2 0 . . .
1079 22588 3 3 2 . . . .
1079 22366 3 3 2 . . . .
1079 22282 3 3 2 0 . 0 .
1079 22571 3 3 2 . . . .
1079 22241 3 3 2 . . . .
1079 22269 3 3 2 . . . .
1079 22170 3 3 2 0 . . .
1079 22599 3 3 2 . . . .
1079 22353 3 3 2 . . . .
1079 22310 3 3 2 0 . 0 .
1079 22445 3 3 2 . . . .
1079 22459 3 3 2 . . . .
1079 22297 3 3 2 . . . .
1079     . 3 3 2 . . . .
1079 22488 3 3 2 . . . .
1079     . 3 3 2 . . . .
1079 22651 3 3 2 . . . .
1079 22325 3 3 2 . . . .
1079 22423 3 3 2 . . . .
1079 22410 3 3 2 . . . .
1079     . 3 3 2 . . . .
1079 22396 3 3 2 . . . .
1079 22213 3 3 2 . . . .
1080 22170 . . . . . . .
1081     . 3 3 3 . . . .
1081 22230 3 3 3 . . . .
1081     . 3 3 3 . . . .
1081 22240 3 3 3 0 . . .
1081 22184 3 3 3 0 . . .
1081 22212 3 3 3 0 . . .
1081 22571 3 3 3 . . . .
1081     . 3 3 3 . . . .
1081     . 3 3 3 . . . .
1081 22352 3 3 3 0 . . .
1081     . 3 3 3 . . . .
1081     . 3 3 3 . . . .
1081 22324 3 3 3 0 . . 0
1081 22425 3 3 3 . . . .
1081     . 3 3 3 . . . .
1081     . 3 3 3 . . . .
1081 22589 3 3 3 . . . .
1081 22370 3 3 3 . . . .
1081 22560 3 3 3 . . . .
1081 22488 3 3 3 . . . .
1081 22590 3 3 3 . . . .
1081 22474 3 3 3 . . . .
1081 22257 3 3 3 . . . .
1081 22268 3 3 3 0 0 . .
1081 22296 3 3 3 0 . 0 .
1081 22380 3 3 3 0 . . .
1081 22653 3 3 3 . . . .
1081 22397 3 3 3 . . . .
1081 22461 3 3 3 . . . .
1081     . 3 3 3 . . . .
1081 22341 3 3 3 . . . .
1081 22313 3 3 3 . . . .
1081 22410 3 3 3 . . . .
1081 22618 3 3 3 . . . .
1082     . . . . . . . .
1083     . . . . . . . .
1084     . . . . . . . .
1085     . 3 2 2 . . . .
1085 22212 3 2 2 0 . . .
1085     . 3 2 2 . . . .
1085     . 3 2 2 . . . .
1085 22240 3 2 2 0 . . .
1085     . 3 2 2 . . . .
1085 22226 3 2 2 . . . .
1085 22268 3 2 2 0 0 . .
1085 22296 3 2 2 0 . 0 .
1085     . 3 2 2 . . . .
1085 22184 3 2 2 0 . . .
1085 22324 3 2 2 0 . . 0
1086 22212 3 3 2 0 . . .
1086 22380 3 3 2 0 . . .
1086 22255 3 3 2 . . . .
1086     . 3 3 2 . . . .
1086 22560 3 3 2 . . . .
1086 22268 3 3 2 0 0 . .
1086 22651 3 3 2 . . . .
1086 22423 3 3 2 . . . .
1086 22339 3 3 2 . . . .
1086     . 3 3 2 . . . .
1086 22397 3 3 2 . . . .
1086 22571 3 3 2 . . . .
1086     . 3 3 2 . . . .
1086 22313 3 3 2 . . . .
1086 22599 3 3 2 . . . .
1086     . 3 3 2 . . . .
1086 22616 3 3 2 . . . .
1086 22369 3 3 2 . . . .
1086 22296 3 3 2 0 . 0 .
1086 22486 3 3 2 . . . .
1086     . 3 3 2 . . . .
1086     . 3 3 2 . . . .
1086 22240 3 3 2 0 . . .
1086     . 3 3 2 . . . .
1086 22459 3 3 2 . . . .
1086 22445 3 3 2 . . . .
1086 22409 3 3 2 . . . .
1086 22473 3 3 2 . . . .
1086 22352 3 3 2 0 . . .
1086 22588 3 3 2 . . . .
1086 22324 3 3 2 0 . . 0
1086     . 3 3 2 . . . .
1088     . 3 2 1 . . . .
1088 22313 3 2 1 . . . .
1088     . 3 2 1 . . . .
1088 22366 3 2 1 0 . . .
1088 22398 3 2 1 . . . .
1088 22257 3 2 1 . . . .
1088     . 3 2 1 . . . .
1088     . 3 2 1 . . . .
1088     . 3 2 1 . . . .
1088 22324 3 2 1 0 . . 0
1088     . 3 2 1 . . . .
1088 22268 3 2 1 0 0 . .
1088     . 3 2 1 . . . .
1088     . 3 2 1 . . . .
1088 22616 3 2 1 . . . .
1088 22579 3 2 1 . . . .
1088 22601 3 2 1 . . . .
1088 22184 3 2 1 0 . . .
1088     . 3 2 1 . . . .
1088 22240 3 2 1 0 . . .
1088 22425 3 2 1 . . . .
1088     . 3 2 1 . . . .
1088 22653 3 2 1 . . . .
1088 22342 3 2 1 . . . .
1088 22411 3 2 1 . . . .
1088     . 3 2 1 . . . .
1088 22226 3 2 1 0 . . .
1088 22380 3 2 1 0 . . .
1088 22589 3 2 1 . . . .
1088 22439 3 2 1 . . . .
1088 22296 3 2 1 0 . 0 .
1088     . 3 2 1 . . . .
1088     . 3 2 1 . . . .
1088 22560 3 2 1 . . . .
1089     . 2 1 1 . . . .
1089 22651 2 1 1 . . . .
1089 22572 2 1 1 . . . .
1089 22212 2 1 1 0 . . .
1089 22324 2 1 1 0 . . 0
1089 22411 2 1 1 . . . .
1089 22445 2 1 1 . . . .
1089 22296 2 1 1 0 . 0 .
1089 22459 2 1 1 . . . .
1089 22312 2 1 1 . . . .
1089 22600 2 1 1 . . . .
1089 22352 2 1 1 . . . .
1089     . 2 1 1 . . . .
1089 22184 2 1 1 0 . . .
1089 22487 2 1 1 . . . .
1089 22341 2 1 1 . . . .
1089     . 2 1 1 . . . .
1089 22618 2 1 1 . . . .
1089 22229 2 1 1 . . . .
1089     . 2 1 1 . . . .
1089 22268 2 1 1 0 0 . .
1089 22398 2 1 1 . . . .
1089 22380 2 1 1 0 . . .
1089     . 2 1 1 . . . .
1089 22588 2 1 1 . . . .
1089 22240 2 1 1 0 . . .
1089     . 2 1 1 . . . .
1089     . 2 1 1 . . . .
1089     . 2 1 1 . . . .
1089     . 2 1 1 . . . .
1089 22560 2 1 1 . . . .
1089     . 2 1 1 . . . .
1089 22475 2 1 1 . . . .
1090 22184 . . . . . . .
1091     . . . . . . . .
1092 22487 3 3 2 . . . .
1092     . 3 3 2 . . . .
end
format %dM_d,_CY study_date
label values vax_status_dec20 vaxstatus
label values vax_status_jan21 vaxstatus
label values vax_status_feb21 vaxstatus
label def vaxstatus 2 "Partially Vaccinated", modify
label def vaxstatus 3 "Unvaccinated", modify
label def vaxstatus 1 "Fully Vaccinated", modify
label values ab_result ab_result_
label def ab_result_ 0 "Negative", modify
label def ab_result_ 1 "Positive", modify
label def ab_result_ 5 "Not submitted", modify
label values abpositive_dec20 yesno
label values abpositive_jan21 yesno
label values abpositive_feb21 yesno
label def yesno 0 "No", modify
label def yesno 1 "Yes", modify
label var unique_id "Unique Study ID" 
label var study_date "Date of study visit" 
label var ab_result "Nucleocapsid antibody test result"

[Clyde Schechter]

OK. Thanks especially for including that ab_result variable: that saved me a ton of work that I would have needed to do to reconstruct it from the ab_positive variables.

I note that there are a huge number of completely duplicate observations in your example data set. Perhaps in the full data set where there are, I presume, additional variables, these duplicates are distinguished by the values of those other variables. But if not, the presence of completely duplicate observations in a data set usually reflects errors in the data management that created the data set. Such errors are easy enough to fix with -duplicates drop-. But where there are some errors, others often lurk. So if your full data set has duplicate observations you should fully review the creation of the data set and fix any errors that were made along the way.

The following code creates, in frame vax_stuff, a fully long layout data set that contains one observation per month per unique_id with the complete ab and vax information for that person (animal?) at that time. It also generates a new variable, new_ab_positive that is 1 for those observations where the unique_id has positive ab result in that month but not in the immediatley preceding month. I believe this is what you were primarily interested in, if I have correctly understood you in #1. Notice how, once the data are efficiently organized, creating this variable takes only 2 lines of code. And, following up on that, I give the cross tabulation of vax_status and new_ab_positive.

I suggest you save the data in frame vax_stuff as a new working data set. Nearly everything you will want to do will be most easily done, or only possible, with the long data.

drop abpositive_*

gen mdate = mofd(study_date)
format mdate %tm

//  CREATE ONE OBSERVATION PER ID PER MONTH FOR AB POSITIVITY
frame put unique_id mdate ab_result, into(ab_stuff)
frame ab_stuff {
    by unique_id mdate, sort: egen ab_positive = max(ab_result == "Positive":ab_result_)
    drop ab_result
    by unique_id mdate: keep if _n == 1
}

//  CREATE ONE OBSERVATION PER ID PER MONTH FOR VACCINATION STATUS
frame put unique_id vax_status_*, into(vax_stuff)
frame change vax_stuff     
egen vmcount = rowmiss(vax_status_*)
quietly ds vax_status_*
local vsvars `r(varlist)'
drop if vmcount == `:word count `vsvars''
duplicates drop
drop vmcount
isid unique_id, sort
reshape long vax_status, i(unique_id) j(_mdate) string
gen mdate = monthly(substr(_mdate, 2, .), "M20Y")
format mdate %tm
drop _mdate

frlink 1:1 unique_id mdate, frame(ab_stuff)
frget ab_positive, from(ab_stuff)

//  IDENTIFY NEW AB POSITIVITY (I.E. AB POSITIVE NOW BUT NOT IN PRECEDING MONTH)
xtset unique_id mdate, monthly
gen byte new_ab_positive = ab_positive == 1 & !(L1.ab_positive == 1)

tab vax_status new_ab_positive

[David Wambui]

Thanks Clyde. This is great coding and I appreciate your help. This is a steep learning process for me dealing with long format data! Especially for the creating of vax status and the ab positivity. You are right with the duplication of observations. So the way the data are included biweekly surveys where individuals report their vax status and monthly Ab positivity determined from blood samples they provide. So in a month, there are two data points for vax status but one for Ab. So it's not a duplicate per se but a biweekly data point will be missing Ab results since there's no sample provided but your code works perfectly for the cross tab for vax status and Ab result. I possibly did not expound the last part clearly enough. So what I want to do is get the number of IDs (it people) that were new Ab positive for each month. So say in December 2020 there were 15 people that were Ab positive but in January 2021 the 15 were still Ab positive but there were 5 new people that became Ab positive. So when tabulating the Abs for January 2021, I want to exclude the 15 that were Ab positive in December 2020. I want to do this for each month.

Thanks,

David

[Clyde Schechter]

To get a monthly tally of the incident cases of Ab positivity, starting from the data set that was left in frame vax_stuff, all you have to do is:

collapse (sum) new_ab_positive, by(mdate)
list, noobs clean

If you want it broken down by vaccination status as well, then instead of -by(mdate)- make it -by(mdate vax_status)-.

Since -collapse- overwrites the data in memory, if you need to go back to that, you can -preserve- it before you do the -collapse- and then -restore- it after you list the results. (Or, if you followed my advice in in #6, you don't need to do that because that data is already saved somewhere anyway and you can just -use- it again when needed.

[David Wambui]

Hi Clyde,

Thanks for helping out here. For Vaccination status, I am having a problem creating one observation per ID per month. When I execute the drop if vmcount == `:word count `vsvars'', it drops all obervations that has vmcount==0 and in this case it drops some IDs which should not be e.g it drops IDs 1002, 1003 etc. I am not sure how I can fix that and I'd appreciate if you can shine some light on that.

Thanks,
David

[Clyde Schechter]

I am having a problem creating one observation per ID per month. When I execute the drop if vmcount == `:word count `vsvars'', it drops all obervations that has vmcount==0 and in this case it drops some IDs which should not be e.g it drops IDs 1002, 1003 etc. I am not sure how I can fix that and I'd appreciate if you can shine some light on that.

When I run the code from #6 with the data in #5, it does not reproduce the problem you are having. The observations with vmcount == 0 are retained, and IDs 1002 and 1003, in particular, are retained. So you must have changed the code in some way, or you are applying it to a data set that is somehow different from the example you posted in #5. In order to troubleshoot this, I need to you to post the exact code that you are having trouble with, and, using -dataex-, a new example data set that exhibits this problem when run with that code.

Added: Wait, I think I see what you're doing wrong. I think you are running the code line by line. This code cannot work that way. The code relies crucially on the use of local macro vsvars. When you run code line-by-line you destroy any local macros immediately after they are created. You must run the code (or at least everything that involves local macro vsvars must be run in one fell swoop).

[David Wambui]

Thanks Clyde. That was indeed the problem. I was running the code line by line. Thanks for the clarification. You are amazing!

[David Wambui]

Hi Clyde,

So I realized the way I created my vaccination status variable was wrong and reshaping it from wide to long would not be helpful. Is there a way I could create the vaccination status variable? What I want to end up with is one vaccination status variable indicating an individual vaccination status at each point in time, say month.

[Clyde Schechter]

Well, the devil is in the details, as they say. First, are the vaccination statuses shown in the example data #4 correct? If not, then you need a data set with correct information as a starting point. Next, what specifically is wrong with the way you created the vaccination status?

Finally, what do you mean by "an individual vaccination status at each point in time?" How is it defined in terms of the raw data values? I ask this because it sounds like there should be some sort of sequence from unvaccinated through partially vaccinated to completely vaccinated--but with some vaccines immunity wanes over time, so that there can be reversion from completely vaccinated to partially vaccinated, and possibly even from partially vaccinated to unvaccinated, at least from the perspective of immunologic effects. So I think you have to layout exactly what data you have as a starting point and what the variables mean, as well as what you want to calculate from them and how those results relate to the raw data. If the starting data do not look like the example in #5, please post a new example.

[David Wambui]

Hi Clyde,

If I have data indicated in the example below, how can I create a variable that identifies a reoccurrence of pcr positive if the individual had an initial pcr positive, then they seroconvert to negative and after a period of >= 90 days they show up with a positive pcr again?

Thanks

David

* Example generated by -dataex-. For more info, type help dataex
clear
input int(unique_id study_date) str8 pcr_result
1001 22281 "Negative"
1001 22312 "Positive"
1001 22369 "Positive"
1001 22385 "Negative"
1001 22428 "Negative"
1001 22446 "Positive"
1002 22281 "Positive"
1002 22312 "Negative"
1002 22369 "Negative"
1002 22385 "Negative"
1002 22428 "Negative"
1002 22446 "Positive"
1003 22281 "Negative"
1003 22312 "Negative"
1003 22369 "Negative"
1003 22385 "Negative"
1003 22428 "Negative"
1003 22446 "Positive"
1004 22281 "Negative"
1004 22312 "Negative"
1004 22369 "Negative"
1004 22385 "Negative"
1004 22428 "Negative"
1004 22446 "Negative"
1005 22281 "Positive"
1005 22312 "Positive"
1005 22369 "Positive"
1005 22385 "Positive"
1005 22428 "Negative"
1005 22446 "Positive"
end
format %tdnn/dd/CCYY study_date

[Clyde Schechter]

by unique_id (study_date), sort: egen first_pos = min(cond(pcr_result == "Positive", study_date, .))
by unique_id (study_date): egen subsequent_neg = ///
    min(cond(pcr_result == "Negative" & study_date > first_pos, study_date, .))
by unique_id (study_date): egen positive_again = ///
    min(cond(!missing(subsequent_neg) & study_date >= 90 + subsequent_neg & pcr_result == "Positive", ///
    study_date, .))
format first_pos subsequent_neg positive_again %tdnn/dd/CCYY
gen byte reoccurrence = !missing(positive_again)

Your question is not entirely clear whether the 90 day period runs from the initial positive or the subsequent negative. I assumed the latter.

While this is, from your perspective, a continuation of the same project that spawned this thread, from the perspective of others who search Statalist for solutions to specific problems, it is off-topic. In the future, when raising new questions, please start a new thread. Also, it's generally not a good idea to address our post to a specific person. It discourages others who might see your post first from responding to it, so you lose time in getting your answer. I would say that addressing your question to a specific person should be reserved for a question that is directly a follow-up to that person's earlier post in the same thread.