unscheduled visit

[teja5959]

Raw Data

subjid visitnum         visit date                            visit
101       1                 27NOV2018               VISIT 1 / BASELINE / DAY 1
101       2                 28NOV2018               VISIT 1 / BASELINE / DAY 1
101       3                29NOV2018                VISIT 1 / BASELINE / DAY 1
101       9.00            06DEC2018               UNSCHEDULED VISIT 1
101       9.00           06DEC2018                UNSCHEDULED VISIT 1
101       9.00           06DEC2018               UNSCHEDULED VISIT 1

102        1              20NOV2018               VISIT 1 / BASELINE / DAY 1

102        2                04DEC2018             VISIT 2 / INTERIM / DAY 15

102        3               18DEC2018              VISIT 3 / END OF STUDY 

102        9.00          01DEC2018                UNSCHEDULED VISIT 1 

102        9.00          01DEC2018                UNSCHEDULED VISIT 1

102        9.00          01DEC2018                UNSCHEDULED VISIT 1

102        9.00          01DEC2018                UNSCHEDULED VISIT 1

Want Data 

subjid visitnum         visit date                            visit
101       1                 27NOV2018               VISIT 1 / BASELINE / DAY 1
101       2                 28NOV2018               VISIT 1 / BASELINE / DAY 1
101       3                29NOV2018                VISIT 1 / BASELINE / DAY 1
101       1.3            06DEC2018                 UNSCHEDULED VISIT 1

102        1              20NOV2018               VISIT 1 / BASELINE / DAY 1

102        1.1            01DEC2018                UNSCHEDULED VISIT 1 

102        2               04DEC2018             VISIT 2 / INTERIM / DAY 15

102        3               18DEC2018              VISIT 3 / END OF STUDY 

[andreas_lds]

Please post the data in usable form, explain the logic that you want applied and explain the difficulties you have finding a solution.

[hashman]

@teja5959 :

If I get your pattern correctly, then try:

data have ;                                                                   
  input subjid visitnum visitdate :date. visit & :$32. ;                      
  cards ;                                                                     
101  1     27NOV2018  VISIT 1 / BASELINE / DAY 1                              
101  2     28NOV2018  VISIT 1 / BASELINE / DAY 1                              
101  3     29NOV2018  VISIT 1 / BASELINE / DAY 1                              
101  9.00  06DEC2018  UNSCHEDULED VISIT 1                                     
101  9.00  06DEC2018  UNSCHEDULED VISIT 1                                     
101  9.00  06DEC2018  UNSCHEDULED VISIT 1                                     
102  1     20NOV2018  VISIT 1 / BASELINE / DAY 1                              
102  2     04DEC2018  VISIT 2 / INTERIM / DAY 15                              
102  3     18DEC2018  VISIT 3 / END OF STUDY                                  
102  9.00  01DEC2018  UNSCHEDULED VISIT 1                                     
102  9.00  01DEC2018  UNSCHEDULED VISIT 1                                     
102  9.00  01DEC2018  UNSCHEDULED VISIT 1                                     
102  9.00  01DEC2018  UNSCHEDULED VISIT 1                                     
run ;                                                                         
                                                                              
proc sql ;                                                                    
  create view hs as select distinct * from have order subjid, visitdate ;     
quit ;                                                                        
                                                                              
data want (drop = _:) ;                                                       
  do until (last.subjid) ;                                                    
    set hs ;                                                                  
    by subjid ;                                                               
    if find (visit, "UNSCHEDULED") then visitnum = sum (1, divide (_vn, 10)) ;
    output ;                                                                  
    _vn = visitnum ;                                                          
  end ;                                                                       
run ;                                                                         

Though I concur with @andreas_lds that when you ask a question, you should present sample data in a usable form, from which the transition of the data\patterns from HAVE to WANT must be clear and unambiguous and easily detected without unnecessary guessing.

Kind regards

Paul D.