DATA Step, Macro, Functions and more

Restart and Continue Sequence (i.e., Time) Based on Three Variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Restart and Continue Sequence (i.e., Time) Based on Three Variables

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.


Accepted Solutions
Solution
‎07-18-2016 04:52 PM
PROC Star
Posts: 1,090

Re: Restart and Continue Sequence (i.e., Time) Based on Three Variables

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;

View solution in original post


All Replies
Solution
‎07-18-2016 04:52 PM
PROC Star
Posts: 1,090

Re: Restart and Continue Sequence (i.e., Time) Based on Three Variables

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;

Respected Advisor
Posts: 4,644

Re: Restart and Continue Sequence (i.e., Time) Based on Three Variables

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?

PG
Occasional Contributor
Posts: 14

Re: Restart and Continue Sequence (i.e., Time) Based on Three Variables

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.  

Trusted Advisor
Posts: 1,204

Re: Restart and Continue Sequence (i.e., Time) Based on Three Variables

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;

Super User
Posts: 9,676

Re: Restart and Continue Sequence (i.e., Time) Based on Three Variables

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;
 
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 325 views
  • 2 likes
  • 5 in conversation