Trouble with reshaping data from long to wide (open to other suggestions)

[Kuber Nadig]

For the life of me I can't figure out how to reshape my data from long to wide, even after googling and reading older posts.

My data consists of individuals that have separate vaccination date records for each dose in 4 different vaccinations. The data presented here is a subset for only 1 vaccination, but I hope to apply some suggestions to the full dataset.

asiis_pat_id float vax_date byte dose_number
 566991 20186 1
 566991 20247 2
 566991 20305 3
 914623 20850 1
 914623 20949 2
 914623 21011 3
 914623 21279 4
 914623 22294 5
 957286 22314 1
 957286 22350 2
 957289 22314 1
 957289 22347 2

I'd like the final data to consist of 1 record for each ID, with different date variables for each dose_numbers, like below. I know it would be better for analysis to keep the data long, so if someone has suggestions on this I'd appreciate it as well

asiis_pat_id float vax_date_dose1 vax_date_dose2 vax_date_dose3 vax_date_dose4 vax_date_dose5 byte
 566991 20186 20247 20305 . 
 914623 20850 20949 21011 21279 22294
 957286 22314 22350 . . .
 957289 22314 22347 . . .

I've tried variations of reshape wide vax_date, i(asiis_pat_id) j(dose_number) but keep getting variations of the same error - values of variable dose_number not unique within asiis_pat_id. Running Stata 17.0 on Windows 10.

Thanks!

[George Ford]

I ran this on what you posted and got a decent result.

reshape wide vax_date , i(asiis_pat_id) j(dose_number)
format vax_* %d

So, I think you've got an error in your data related to a repeated vaccination date for one or more observations.

bys asiis_pat_id (dose_number): g dup = dose_number[_n] == dose_number[_n-1]
list asiis_pat_id if dup==1

[Kuber Nadig]

Hey George, you were right - a combo of duplicates and missing values causing issues. Tried again and works as expected. Thanks!