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
Please post the data in usable form, explain the logic that you want applied and explain the difficulties you have finding a solution.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.