have: 3+ readings per visit. need: 2 readings per visit, only 1 for each eye

have: 3+ readings per visit. need: 2 readings per visit, only 1 for each eye

by Amanda -

Apologies to my classmates for the multipe postings.

I am preparing to reshape my data from long to wide.

This is what I have:

     |  pt_id         OD        OS   visit1 |
     |--------------------------------------|
  7. | 702337      20/50                  1 |
  8. | 702337                20/30        1 |
  9. | 702337    20/40-2                  1 |
 10. | 702337              20/25        2 |
 11. | 702337   20/60                  2 |


This is where I am headed:

pt_ID OD1 OS1 OD2 OS2
702377 20/40-2 20/30 20/60 20/25


OD: right eye, OS: left eye

However, you see in my long data that there are 2 readings for the right eye (OD) on the first visit.
This happens for a few patients throughout my dataset.
I need to keep 2 readings per 1 visit:  1 from the right eye and 1 from the left eye.

What I need to do is take the "latest" reading (row furthest down) for either eye, meaning I need to drop the earlier readings for all patients who have more than 1 reading in 1 eye for any single visit.

How can I go about doing so?

Thank you for your help!

Re: have: 3+ readings per visit. need: 2 readings per visit, only 1 for each eye

by Sofia Verstraete -

Although I’m sure there are more elegant solutions, here is a suggestion:

Convert to a wide dataset by visit first.  To do this you will have to create a new variable where you assign a number to each observation of a visit. 

      |  pt_id         OD        OS   visit1 |  newvar

     |--------------------------------------|
  7. | 702337      20/50                  1 |   1
  8. | 702337                20/30        1 |   2
  9. | 702337    20/40-2                  1 |   3
 10. | 702337              20/25          2 |   1
 11. | 702337   20/60                     2 |   2

To do this the command would look something like this:

by visit:  gen newvar = cond(_N==1,0,_n)

Then you convert to wide use newvar as your “j” variable, which should look something like this:

 reshape wide OD OS (and other var), i(visit) j(newvar)

 And should result in:

pt_ID

OD1

OS1

OD2

OS2

OD3

OS3

visit

702377

20/40-2

.

.

20/30

20/60

.

1

702377

.

20/25

20/60

.

 

 

2

 

From there you can create a command to replace OD1 with OD2 or OD3 if they are different (!= in STATA lingo) from missing(.) and then just delete the extra column.  Just be careful and make sure that all the latest values for OD and OS are in the correct column before dropping it. 

For other people who may be reading this post and are wondering what “reshaping” means, it is a way to change a dataset when there are multiple observations (visits, etc) per subject so that there is only row per subject, which is generally needed for analysis. Here is the example from the STATA command search. 

            long

        +------------+                  wide

        | i  j  stub |                 +----------------+

        |------------|                 | i  stub1 stub2 |

        | 1  1   4.1 |     reshape     |----------------|

        | 1  2   4.5 |   <--------->   | 1    4.1   4.5 |

        | 2  1   3.3 |                 | 2    3.3   3.0 |

        | 2  2   3.0 |                 +----------------+

        +------------+

 

Hope this helps.  

Re: have: 3+ readings per visit. need: 2 readings per visit, only 1 for each eye

by Amanda -

Great, thank you!