SCHEDULED VISIT VS UNSCHEDULED VISIT

[singhsahab]

Hi All,

My requirement is as mention below

1.If any UNSC visit happens same day as scheduled visit (week1,week2,week3) then visitnumber should be scheduled visitnumber+0.01.
2. If any UNSC visit happens before  week1 (week1 is first visit) then visitnumber should be start from 999.01 and carry on like 999.01 + 0.01 until week1 visit date
3. If any UNSC happen after all the visit then last visit(week3) visitnumber should be get added unlit last UNSC visit.

here is sample data with expected output data.

data have;
input subject visitnumber visit : $5. visdt $10. ;
cards;
100 1 week1 01-01-2020
100 2 week2 02-01-2020
100 3 week3 03-01-2020
100 . UNSC 02-01-2020
100 . UNSC 04-01-2020
100 . UNSC 05-01-2020
100 . UNSC 3-12-2019
100 . UNSC 5-12-2019
;
run;
/*required output*/


data WANT;
input subject visitnumber visit : $5. visdt $10. ;
cards;
100 999.01 UNSC 3-12-2019
100 999.02 UNSC 5-12-2019
100 1 week1 01-01-2020
100 2 week2 02-01-2020
100 2.1 UNSC 02-01-2020
100 3 week3 03-01-2020
100 3.01 UNSC 04-01-2020
100 3.02 UNSC 05-01-2020
;
run;

Thank you so much for your kind support  !!

[data_null__ · Accepted solution]

Something like this I reckon.

data have;
input subject visitnum visit: $5. visdt :mmddyy.;
format visdt yymmdd.;
cards;
100 1 week1 01-01-2020
100 2 week2 02-01-2020
100 3 week3 03-01-2020
100 . UNSC 02-01-2020
100 . UNSC 04-01-2020
100 . UNSC 05-01-2020
100 . UNSC 3-12-2019
100 . UNSC 5-12-2019
;
run;
proc sort;
   by subject visdt;
   run;
proc print;
   run;
data want;
   set have;
   by subject visitnum notsorted;
   retain v;
   if first.visitnum then do;
      y=0;      
      end;
   if not missing(visitnum) then v=int(visitnum);
   if visit eq 'UNSC' then do;
      y+.01;
      visitnum = coalesce(v,999) + y;
      end;
   run;
proc print;
   run;