Hi
That is a bit tricky, because it requires the opposite of LAG, and there are no look-ahead functions in SAS, has probably something to do with the way observations are brought into the program vector. But years back a smart guy came up with a solution at a SAS conference.
So here wo go:
* Test data;
data have;
length Visit $10 Date 8;
format date date9.;
Visit = 'C1 Day 1'; DATE='01AUG2017'd; output;
Visit = 'C1 Day 5'; DATE='04AUG2017'd; output;
Visit = 'C2 Day 1'; DATE='15AUG2017'd; output;
Visit = 'C2 Day 5'; DATE='19AUG2017'd; output;
Visit = 'C3 Day 1'; DATE='25AUG2017'd; output;
Visit = 'C3 Day 15';DATE='05SEP2017'd; output;
Visit = 'C4 Day 1';DATE='10SEP2017'd; output;
run;
* Create Cycle variable and make sure input is in right order;;
proc sql;
create table inter1 as
select Visit, Date,
Tranwrd(scan(Visit,1,' '),'C','Cycle ') as Cycle
from want
order by Cycle, Date;
quit;
* Set start and end;
* Trick simulating look-ahead by using 2 set statements, one with firstobs=2;
data inter2 (keep=Cycle CycleStart CycleEnd);
format CycleStart cycleEnd date9.;
retain CycleStart;
if eof=0 then set inter1(firstobs=2 keep=Cycle Date rename=(Cycle=nextCycle date=nextDate)) end=eof;
set inter1; by Cycle;
if first.Cycle then cycleStart = Date;
if last.Cycle then do;
cycleEnd = nextDate-1;
if cycleStart < cycleEnd then output;
end;
run;
* Join all cycles/dates with start/end;
proc sql;
create table want as
select
a.Visit,
a.Date,
b.Cycle,
b.cycleStart,
b.cycleEnd
from inter1 as a left join inter2 as b
on a.Cycle = b.Cycle
order by
a.Cycle,
a.Date;
quit;
... View more
