Can anyone help me to derive the following scenario.
My raw data;
data dov;
input SUBJID visdate $6-15 VISITNUM 16-17 VISIT $18-25;
datalines;
1001 10Jan2015 1 VISIT1
1001 15Jan2015 2 VISIT2
1001 20Jan2015 3 VISIT3
1001 25Jan2015 4 VISIT4
1001 30Jan2015 5 VISIT5
1002 04Feb2015 1 VISIT1
1002 09Feb2015 2 VISIT2
1002 14Feb2015 3 VISIT3
1002 19Feb2015 4 VISIT4
1002 24Feb2015 5 VISIT5
1002 01Mar2015 6 VISIT6
1003 06Mar2015 1 VISIT1
1003 11Mar2015 2 VISIT2
1003 16Mar2015 3 VISIT3
1003 21Mar2015 4 VISIT4
1003 26Mar2015 5 VISIT5
1003 31Mar2015 6 VISIT6
1003 08Mar2015 UNSCH
1003 19Mar2015 UNSCH
1003 19Mar2015 UNSCH
1003 20Mar2015 UNSCH
1001 11Jan2015 UNSCH
1001 12Jan2015 UNSCH
1001 13Jan2015 UNSCH
1001 13Jan2015 UNSCH
1002 02Mar2015 UNSCH
1002 02Mar2015 UNSCH
1002 03Mar2015 UNSCH
1002 05Feb2015 UNSCH
1002 05Feb2015 UNSCH
;
run;
Need output like below;
subjid date visitnum visit
1001 10Jan2015 1 VISIT1
1001 11Jan2015 1.01 UNSCHEDULED 1.01
1001 12Jan2015 1.02 UNSCHEDULED 1.02
1001 13Jan2015 1.03 UNSCHEDULED 1.03
1001 13Jan2015 1.03 UNSCHEDULED 1.03
the highlighted red color is same date record visit number also same.
Mycode:
proc sort data = dov;
by SUBJID visdate;
run;
data UNSCH_date;
set dov;
if VISITNUM ne . then New_Visitnum = VISITNUM;
else New_Visitnum+0.01;
drop VISITNUM;
rename New_Visitnum = VISITNUM;
run;
my output screenshot is attached. But it's not the exact result.
Thanks
Sures
Reading visdate as a SAS date instead of a character variable should help:
informat visdate date9.;
format visdate date9.;
input SUBJID visdate VISITNUM 16-17 VISIT $18-25;
Is this what you are looking for ?
data dov;
input SUBJID $ visdate :date9. VISITNUM VISIT :$15.;
infile cards truncover;
cards;
1001 10Jan2015 1 VISIT1
1001 15Jan2015 2 VISIT2
1001 20Jan2015 3 VISIT3
1001 25Jan2015 4 VISIT4
1001 30Jan2015 5 VISIT5
1002 04Feb2015 1 VISIT1
1002 09Feb2015 2 VISIT2
1002 14Feb2015 3 VISIT3
1002 19Feb2015 4 VISIT4
1002 24Feb2015 5 VISIT5
1002 01Mar2015 6 VISIT6
1003 06Mar2015 1 VISIT1
1003 11Mar2015 2 VISIT2
1003 16Mar2015 3 VISIT3
1003 21Mar2015 4 VISIT4
1003 26Mar2015 5 VISIT5
1003 31Mar2015 6 VISIT6
1003 08Mar2015 . UNSCHEDULED
1003 19Mar2015 . UNSCHEDULED
1003 19Mar2015 . UNSCHEDULED
1003 20Mar2015 . UNSCHEDULED
1001 11Jan2015 . UNSCHEDULED
1001 12Jan2015 . UNSCHEDULED
1001 13Jan2015 . UNSCHEDULED
1001 13Jan2015 . UNSCHEDULED
1002 02Mar2015 . UNSCHEDULED
1002 02Mar2015 . UNSCHEDULED
1002 03Mar2015 . UNSCHEDULED
1002 05Feb2015 . UNSCHEDULED
1002 05Feb2015 . UNSCHEDULED
;
run;
proc sort data = dov;
by SUBJID visdate;
run;
data want;
set dov;
by SUBJID visdate;
if first.SUBJID then New_Visitnum =VISITNUM;
else do;
if VISIT eq 'UNSCHEDULED' then New_Visitnum+0.01;
else New_Visitnum =VISITNUM;
end;
run;
I believe you need to amend your logic as below so that visitnum for unscheduled doesn't get incremented for the 2nd to nth occurrence on the same date.
data want;
set dov;
by SUBJID visdate;
if first.SUBJID then New_Visitnum =VISITNUM;
else
do;
if VISIT eq 'UNSCHEDULED' then
do;
if first.visdate then New_Visitnum+0.01;
end;
else New_Visitnum =VISITNUM;
end;
run;
Thanks a lot sir, its simple and effective out. Great.....
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.