BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PhillipSherlock
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

5 REPLIES 5
TomKari
Onyx | Level 15

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;

PGStats
Opal | Level 21

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
PhillipSherlock
Obsidian | Level 7

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.  

stat_sas
Ammonite | Level 13

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;

Ksharp
Super User

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;
 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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