Below is an example of the data I would like to create. I have the Study, Case and Phase variables. I need help creating the Time variable. Time must restart every time Phase changes from 0 to 1. However, if Phase returns to 0 or 1 for the same Case in the same Study, Time should continue counting from the previous instance of that particular Phase.
Study | Case | Phase | Time |
1 | 1 | 0 | 1 |
1 | 1 | 0 | 2 |
1 | 1 | 1 | 1 |
1 | 1 | 1 | 2 |
1 | 1 | 0 | 3 |
1 | 1 | 0 | 4 |
1 | 1 | 1 | 3 |
1 | 1 | 1 | 4 |
1 | 2 | 0 | 1 |
1 | 2 | 0 | 2 |
1 | 2 | 1 | 1 |
1 | 2 | 1 | 2 |
1 | 2 | 0 | 3 |
1 | 2 | 0 | 4 |
1 | 2 | 1 | 3 |
1 | 2 | 1 | 4 |
2 | 1 | 0 | 1 |
2 | 1 | 0 | 2 |
2 | 1 | 1 | 1 |
2 | 1 | 1 | 2 |
3 | 1 | 0 | 1 |
3 | 1 | 0 | 2 |
3 | 1 | 1 | 1 |
3 | 1 | 1 | 2 |
3 | 1 | 0 | 3 |
3 | 1 | 0 | 4 |
3 | 1 | 1 | 3 |
3 | 1 | 1 | 4 |
3 | 2 | 0 | 1 |
3 | 2 | 0 | 2 |
3 | 2 | 1 | 1 |
3 | 2 | 1 | 2 |
3 | 2 | 0 | 3 |
3 | 2 | 0 | 4 |
3 | 2 | 1 | 3 |
3 | 2 | 1 | 4 |
Thank you for your help.
data want;
set have;
retain Time _Time;
drop _:;
by Study notsorted Case notsorted Phase notsorted;
if first.Case
then do;
Time = 1;
_Time = Time;
end;
else if first.Phase then
if Phase = 0 then do;
Time = Time + 1;
_Time = Time;
end;
else do;
Time = _Time;
end;
else Time = Time + 1;
run;
data want;
set have;
retain Time _Time;
drop _:;
by Study notsorted Case notsorted Phase notsorted;
if first.Case
then do;
Time = 1;
_Time = Time;
end;
else if first.Phase then
if Phase = 0 then do;
Time = Time + 1;
_Time = Time;
end;
else do;
Time = _Time;
end;
else Time = Time + 1;
run;
Please explain this particular sequence:
Study | Case | Phase | Time |
1 | 1 | 0 | 3 |
1 | 1 | 0 | 4 |
1 | 1 | 1 | 3 |
1 | 1 | 1 | 4 |
Why does Time go back to 3?
That is the second instance where Case 1 from Study 1 is in Phase 1. The last time point from the first instance of Case 1 from Study 1 in Phase 1 was Time = 2. Thus, in the second instance of Case 1 from Study 1 where Phase = 2, Time will continue starting with 3.
Hi,
Please try this. You need to create id variable to bring back data in its original form. As per desired output you need to sort it by phase variable that will bring data in a sequence (0s and 1s together) and from there iterate time variable. Once this is done you can bring back data in original shape by sorting it id variable.
data have;
input Study Case Phase;
id=_n_;
datalines;
1 1 0
1 1 0
1 1 1
1 1 1
1 1 0
1 1 0
1 1 1
1 1 1
1 2 0
1 2 0
1 2 1
1 2 1
1 2 0
1 2 0
1 2 1
1 2 1
2 1 0
2 1 0
2 1 1
2 1 1
3 1 0
3 1 0
3 1 1
3 1 1
3 1 0
3 1 0
3 1 1
3 1 1
3 2 0
3 2 0
3 2 1
3 2 1
3 2 0
3 2 0
3 2 1
3 2 1
;
proc sort data=have;
by Study Case Phase;
run;
data want;
set have;
by Study Case Phase;
if first.study or first.case or first.Phase then Time=0;
Time+1;
run;
proc sort data=want out=want(drop=id);
by id;
run;
Of course. I would not miss such question. Assuming I know what you are looking for .
data have;
input Study Case Phase;
datalines;
1 1 0
1 1 0
1 1 1
1 1 1
1 1 0
1 1 0
1 1 1
1 1 1
1 2 0
1 2 0
1 2 1
1 2 1
1 2 0
1 2 0
1 2 1
1 2 1
2 1 0
2 1 0
2 1 1
2 1 1
3 1 0
3 1 0
3 1 1
3 1 1
3 1 0
3 1 0
3 1 1
3 1 1
3 2 0
3 2 0
3 2 1
3 2 1
3 2 0
3 2 0
3 2 1
3 2 1
;
run;
data want;
set have;
by Study Case;
retain temp found initial;
lag_Phase=lag(Phase);
if first.Case then do;
n=0;temp=0;initial=0;found=0;
end;
else if Phase=1 and lag_Phase=0 then do;
n=0;
if found then initial=temp;
end;
else if Phase=0 and lag_Phase=1 then do;temp=n;found=1;end;
n+1;
time=sum(n,initial);
drop n temp found initial lag_Phase;
run;
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.