I have the data below.
data have ;
input id month enrol_period disenrollment reenrollment event case ;
cards ;
1 0 1 0 0 0 1
1 1 1 0 0 0 1
1 2 1 0 0 0 1
1 3 1 0 0 0 1
1 4 -1 1 0 0 1
1 5 -1 0 0 0 1
1 6 -1 0 0 0 1
1 7 2 0 1 0 1
1 8 2 0 0 0 1
1 9 2 0 0 0 1
1 10 2 0 0 1 1
2 0 1 0 0 0 0
2 1 1 0 0 0 0
2 2 1 0 0 0 0
2 3 1 0 0 0 0
2 4 1 0 0 0 0
2 5 1 0 0 0 0
2 6 1 0 0 0 0
2 7 1 0 0 0 0
2 8 1 0 0 0 0
2 9 1 0 0 0 0
2 10 1 0 0 0 0 ;
run;
I want to have the data below. The case status has been reset to 0, during the months which period is -1 and before.
Multiple disenrollment and reenrollment can happen during follow-up. Event is one of the end-points of the study. If event happens, I want to consider it only for the last period of enrollment, not entire follow-up.
data want ;
input id month enrol_period disenrollment reenrollment event case ;
cards ;
1 0 1 0 0 0 0
1 1 1 0 0 0 0
1 2 1 0 0 0 0
1 3 1 0 0 0 0
1 4 -1 1 0 0 0
1 5 -1 0 0 0 0
1 6 -1 0 0 0 0
1 7 2 0 1 0 1
1 8 2 0 0 0 1
1 9 2 0 0 0 1
1 10 2 0 0 1 1
2 0 1 0 0 0 0
2 1 1 0 0 0 0
2 2 1 0 0 0 0
2 3 1 0 0 0 0
2 4 1 0 0 0 0
2 5 1 0 0 0 0
2 6 1 0 0 0 0
2 7 1 0 0 0 0
2 8 1 0 0 0 0
2 9 1 0 0 0 0
2 10 1 0 0 0 0
;
run;
Any ideas to resolve this are appreciated. Thanks.
Please try the below code
data have ;
input id month enrol_period disenrollment reenrollment event case ;
if enrol_period=-1 then newvar=month;
cards ;
1 0 1 0 0 0 1
1 1 1 0 0 0 1
1 2 1 0 0 0 1
1 3 1 0 0 0 1
1 4 -1 1 0 0 1
1 5 -1 0 0 0 1
1 6 -1 0 0 0 1
1 7 2 0 1 0 1
1 8 2 0 0 0 1
1 9 2 0 0 0 1
1 10 2 0 0 1 1
2 0 1 0 0 0 0
2 1 1 0 0 0 0
2 2 1 0 0 0 0
2 3 1 0 0 0 0
2 4 1 0 0 0 0
2 5 1 0 0 0 0
2 6 1 0 0 0 0
2 7 1 0 0 0 0
2 8 1 0 0 0 0
2 9 1 0 0 0 0
2 10 1 0 0 0 0
;
run;
proc sort data=have;
by id descending newvar;
run;
data want;
set have;
by id descending newvar;
retain newvar2;
if first.id then newvar2=newvar;
if month<=newvar2 then case=0;
run;
proc sort data=want;
by id month;
run;
Please try the below code
data have ;
input id month enrol_period disenrollment reenrollment event case ;
if enrol_period=-1 then newvar=month;
cards ;
1 0 1 0 0 0 1
1 1 1 0 0 0 1
1 2 1 0 0 0 1
1 3 1 0 0 0 1
1 4 -1 1 0 0 1
1 5 -1 0 0 0 1
1 6 -1 0 0 0 1
1 7 2 0 1 0 1
1 8 2 0 0 0 1
1 9 2 0 0 0 1
1 10 2 0 0 1 1
2 0 1 0 0 0 0
2 1 1 0 0 0 0
2 2 1 0 0 0 0
2 3 1 0 0 0 0
2 4 1 0 0 0 0
2 5 1 0 0 0 0
2 6 1 0 0 0 0
2 7 1 0 0 0 0
2 8 1 0 0 0 0
2 9 1 0 0 0 0
2 10 1 0 0 0 0
;
run;
proc sort data=have;
by id descending newvar;
run;
data want;
set have;
by id descending newvar;
retain newvar2;
if first.id then newvar2=newvar;
if month<=newvar2 then case=0;
run;
proc sort data=want;
by id month;
run;
Thank you so much Jag. This answer is exactly what I was looking for.
I appreciate your help.
Best,
Bahareh
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!
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.