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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.