Hello Folks,
I have a typical situation here to handle.
Client gave me several visits and wanted to modify(adding prefix to unscheduled visits) the visits based on the previous or next visits, the problem here is we dont even know when the unscheduled visit comes.
Here is the snapshot of the scenario.
Kindly, help me with this
subject visit Expected Visit
101 baseline baseline
101 unscheduled 01 MTN Unscheduled 01
101 unscheduled 02 MTN Unscheduled 02
101 unscheduled 03 MTN Unscheduled 03
101 MTN week 07 MTN week 07
101 MTN WEEK 13 MTN WEEK 13
101 REI baseline REI baseline
101 REI WEEK 07 REI WEEK 07
101 UNSCHEDULED 01 REI UNSCHEDULED 01
101 UNSCHEDULED 02 REI UNSCHEDULED 02
103 REI WEEK 13 REI WEEK 13
Well, I had to make a few assumptions about your desired logic, but here's a program that creates your desired output from your input.
Hope it helps!
Tom
data Inter01;
set Have;
SeqNo = _n_;
run;
data Inter02;
length ExpectedVisit $20 OldPrefix $20 CurrentPrefix $20 FirstWord $20;
retain OldPrefix;
set Inter01;
by Subject;
if first.Subject then OldPrefix = " ";
FirstWord = scan(Visit, 1);
if lowcase(FirstWord) ^= "unscheduled" & lowcase(FirstWord) ^= "baseline"
then do;
OldPrefix = FirstWord;
ExpectedVisit = Visit;
end;
else if CurrentPrefix = "" & OldPrefix ^= ""
then do;
CurrentPrefix = OldPrefix;
if lowcase(FirstWord) = "unscheduled"
then ExpectedVisit = catx(" ", CurrentPrefix, Visit);
else ExpectedVisit = Visit;
end;
drop OldPrefix;
run;
proc sort data=Inter02 out=Inter03;
by descending SeqNo;
run;
data Inter04;
length ExpectedVisit $20 OldPrefix $20 CurrentPrefix $20 FirstWord $20;
retain OldPrefix;
set Inter03;
by notsorted Subject;
if first.Subject then OldPrefix = " ";
FirstWord = scan(Visit, 1);
if lowcase(FirstWord) ^= "unscheduled" & lowcase(FirstWord) ^= "baseline"
then do;
OldPrefix = FirstWord;
ExpectedVisit = Visit;
end;
else if CurrentPrefix = "" & OldPrefix ^= ""
then do;
CurrentPrefix = OldPrefix;
if lowcase(FirstWord) = "unscheduled"
then ExpectedVisit = catx(" ", CurrentPrefix, Visit);
else ExpectedVisit = Visit;
end;
run;
proc sql noprint;
create table Want as
select Subject, Visit, ExpectedVisit
from Inter04
order by SeqNo;
quit;
sorry typo, last visit is also of the same subject 101
Well, I had to make a few assumptions about your desired logic, but here's a program that creates your desired output from your input.
Hope it helps!
Tom
data Inter01;
set Have;
SeqNo = _n_;
run;
data Inter02;
length ExpectedVisit $20 OldPrefix $20 CurrentPrefix $20 FirstWord $20;
retain OldPrefix;
set Inter01;
by Subject;
if first.Subject then OldPrefix = " ";
FirstWord = scan(Visit, 1);
if lowcase(FirstWord) ^= "unscheduled" & lowcase(FirstWord) ^= "baseline"
then do;
OldPrefix = FirstWord;
ExpectedVisit = Visit;
end;
else if CurrentPrefix = "" & OldPrefix ^= ""
then do;
CurrentPrefix = OldPrefix;
if lowcase(FirstWord) = "unscheduled"
then ExpectedVisit = catx(" ", CurrentPrefix, Visit);
else ExpectedVisit = Visit;
end;
drop OldPrefix;
run;
proc sort data=Inter02 out=Inter03;
by descending SeqNo;
run;
data Inter04;
length ExpectedVisit $20 OldPrefix $20 CurrentPrefix $20 FirstWord $20;
retain OldPrefix;
set Inter03;
by notsorted Subject;
if first.Subject then OldPrefix = " ";
FirstWord = scan(Visit, 1);
if lowcase(FirstWord) ^= "unscheduled" & lowcase(FirstWord) ^= "baseline"
then do;
OldPrefix = FirstWord;
ExpectedVisit = Visit;
end;
else if CurrentPrefix = "" & OldPrefix ^= ""
then do;
CurrentPrefix = OldPrefix;
if lowcase(FirstWord) = "unscheduled"
then ExpectedVisit = catx(" ", CurrentPrefix, Visit);
else ExpectedVisit = Visit;
end;
run;
proc sql noprint;
create table Want as
select Subject, Visit, ExpectedVisit
from Inter04
order by SeqNo;
quit;
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 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.