topic Deleting observations from a certain point forward in SAS Data Management

Deleting observations from a certain point forward

bgosiker — Mon, 01 Apr 2019 15:19:31 GMT

Hi All,

I have longitudinal data (long data format) and am trying to drop observations for people who have been lost to follow-up. I am defining this as people who missed 2 visits or more in a row. I made a "visitdiff" variable (which SHOULD be 1 if they have missed no visits), if it is >=2 then I'd like to drop all observations for that person moving forward since I am considering them lost to follow up.

Data Structure:

ID	visit	lag_visit	visitdiff
1	1	.	.
1	2	1	1
1	5	2	3
1	6	5	1
1	7	6	1
1	8	7	1
1	9	8	1
2	1	.	.
2	2	1	1
2	3	2	1
3	1	.	.
3	2	1	1
3	3	2	1
3	5	3	2
3	6	5	1

In the data above, for id=1, I'd want to delete all data after visit 2 since the next visit was visit 5 (ie 2 missed visits). Here is the code I currently have to generate the visit differences based off of lag_visit. Any help on deleting all observations after the first instance of visitdiff>2 (since 2 missed visits would make visitdiff=3) for a participant would be greatly appreciated!

data Aim2.want; set Aim2.have; 
	by id visit;
	lag_visit=lag(visit); 
	visitdiff=visit-lag_visit; 
	if first.id then visitdiff=.;
run;

Re: Deleting observations from a certain point forward

PaigeMiller — Mon, 01 Apr 2019 15:32:58 GMT

UNTESTED CODE


data Aim2.want; 
        retain flag;
        set Aim2.have; 
	by id visit;
	lag_visit=lag(visit); 
	visitdiff=visit-lag_visit; 
	if first.id then do;
            visitdiff=.;
            flag=0;
        end;
        if visitdiff>=2 then flag=1;
run;

then you can delete all observations where flag=1

Re: Deleting observations from a certain point forward

bgosiker — Mon, 01 Apr 2019 19:46:10 GMT

Thanks! When I try this is looks like it carries forward flag=1 through almost all of the dataset (multiple id's) instead of restarting at each id.

Re: Deleting observations from a certain point forward

PaigeMiller — Mon, 01 Apr 2019 19:49:35 GMT

Well, I said it was untested code. I don't think it should do that, can you show us the code you actually used and the result?

And can you provide your data as an actual SAS data step rather than in the form you provided it?

Re: Deleting observations from a certain point forward

ballardw — Mon, 01 Apr 2019 22:25:22 GMT

An example of how to provide data and possible solution:

Data have;
 input ID  visit  lag_visit visitdiff ;
datalines;
1 1 . . 
1 2 1 1 
1 5 2 3 
1 6 5 1 
1 7 6 1 
1 8 7 1 
1 9 8 1 
2 1 . . 
2 2 1 1 
2 3 2 1 
3 1 . . 
3 2 1 1 
3 3 2 1 
3 5 3 2 
3 6 5 1 
;
run;

data want; 
   set have;
   by id;
   retain drop ;
   if first.id then drop=0;
   if visitdiff>1 then drop=1;
   if drop=0;
   drop drop;
run;

Re: Deleting observations from a certain point forward

bgosiker — Thu, 04 Apr 2019 18:18:36 GMT

Sorry for delayed response -- I actually realized something other code that I was trying to institute in the same data step was messing it up a bit. Once I separated my other bits of code (deleting some specific observations) from it then the code you provided worked perfectly!