remapping of unscheduled visits

[Nickjos]

My data is like this. I have to remap the unscheduled visits to the closest scheduled visit that is missing. Unscheduled visit of 2 on aDDY=1 can not be re-mapped, since we already have the screening visit.  The unscheduled visit on ADDY=108 for 0 can be re-mapped to Day 85 . The unscheduled visit for 1 on day 57 can be re-mapped to Day 43.

Can someone guide how to proceed...

SUB   VISITNUM  VISIT         AVISIT        ADDY 
---------------------------------------------------------------------------------------
  2         2  Screening     Screening      -14 
  2        11  Day 43        Day 43          43 
  2        18  Day 85        Day 85     91 
  2        99  Unscheduled   Unscheduled      1 
  7         2  Screening     Screening      -12 
  7        11  Day 43        Day 43          43 
  7        18  Day 85        Day 85          87 
  7        99  Unscheduled   Unscheduled      9 
  0         2  Screening     Screening      -12 
  0        11  Day 43        Day 43          39 
  0        99  Unscheduled   Unscheduled    108 
  3         2  Screening     Screening       -5 
  3        11  Day 43        Day 43          45 
  3        18  Day 85 (EOT)  Day 85 (EOT)    85 
  3        99  Unscheduled   Unscheduled     99 
  1         2  Screening     Screening      -51 
  1        18  Day 85 (EOT)  Day 85 (EOT)    84 
  1        99  Unscheduled   Unscheduled     57

[ballardw]

---

@Nickjos wrote:

My data is like this. I have to remap the unscheduled visits to the closest scheduled visit that is missing. Unscheduled visit of 2 on aDDY=1 can not be re-mapped, since we already have the screening visit.  The unscheduled visit on ADDY=108 for 0 can be re-mapped to Day 85 . The unscheduled visit for 1 on day 57 can be re-mapped to Day 43.

Can someone guide how to proceed...

SUB   VISITNUM  VISIT         AVISIT        ADDY 
---------------------------------------------------------------------------------------
  2         2  Screening     Screening      -14 
  2        11  Day 43        Day 43          43 
  2        18  Day 85        Day 85     91 
  2        99  Unscheduled   Unscheduled      1 
  7         2  Screening     Screening      -12 
  7        11  Day 43        Day 43          43 
  7        18  Day 85        Day 85          87 
  7        99  Unscheduled   Unscheduled      9 
  0         2  Screening     Screening      -12 
  0        11  Day 43        Day 43          39 
  0        99  Unscheduled   Unscheduled    108 
  3         2  Screening     Screening       -5 
  3        11  Day 43        Day 43          45 
  3        18  Day 85 (EOT)  Day 85 (EOT)    85 
  3        99  Unscheduled   Unscheduled     99 
  1         2  Screening     Screening      -51 
  1        18  Day 85 (EOT)  Day 85 (EOT)    84 
  1        99  Unscheduled   Unscheduled     57

---

You are goin to have to provide some more rules. Such as what role does the variable SUB play? The position you show Sub in would make me think it may be short for something like "subject" or a person identifier. In which case mapping the 108 from Sub=0 as 85 from Sub=7 makes little sense. And what is the rule that would get the 57 for 1 to day 43. I'm really not sure I understand what any of the rules actually are in terms of using the data to select the correct values. And is the text "unscheduled" supposed to change?

[hashman]

@Nickjos:

If you want some sensible assistance, please:

• As @ballardw already said, provide clearly-stated rules that can be used to construct programming logic.
• Show how you want you output data to look like, so that those who'd like to volunteer to help you wouldn't have to waste their time guessing.  

Kind regards

Paul D.