getting the last known status from multiple time points

[axescot78]

I have patient data where they come in on the first visit and are scheduled follow up visits. The values are binary except when they miss a visit (blank). I would like to create 4 new variables for each visit that will be the last known non-missing status from the last visit. So from the fake data below, for the last patient (0219):

lks_v2 = 0

lks_v3 = 0

lks_v4 = 0

lks_v5 = 1

I know I can do this with a bunch of if statements but is there a more elegant way? I was thinking to use coalesce statements.

data fake_data;

input patID $ 1-4
	visit_1 $ 6
	visit_2 $ 8
	visit_3 $ 10
	visit_4 $ 12
	visit_5 $ 14
;

datalines;
1693 1 1 . . 0
0659 0 . . 1 0
0389 1 0 . 1 0
0744 1 . 0 1 0
1129 0 1 . . 1
1003 0 0 1 . 1
0734 1 . 0 . 1
1345 1 . 1 . 0
0219 0 . . 1 1
;

[andreas_lds]

I fixed some trouble with the fake data you posted:

 data fake_data;
   length patId $ 4 visit_1-visit_5 8;
   input patID visit_1 - visit_5;
 datalines;
 1693 1 1 . . 0
 0659 0 . . 1 0
 0389 1 0 . 1 0
 0744 1 . 0 1 0
 1129 0 1 . . 1
 1003 0 0 1 . 1
 0734 1 . 0 . 1
 1345 1 . 1 . 0
 0219 0 . . 1 1
 ;

Maybe something like this:

data want;
   set fake_data;
   
   length lks_v2 - lks_v5 8;
   array v visit_2 - visit_5;
   array b lks_v2 - lks_v5;
   
   hasOne = 0;
     
   do i = dim(b) to 1 by -1;
      b[i] = v[i] and not hasOne;
      hasOne = b[i];
   end;
   
   drop i hasOne;
run;

[axescot78]

this didn't work 😞

how do I specify going to the prior element? v[i-1] did not work.

[axescot78 · Accepted solution]

I found this to work:

data want;
	set fake_data;	
	
	/* assigns first non-missing follow up values */
	do;
		lks_v2 = visit_1;
		lks_v3 = coalesce(of visit_2 - visit_1);
		lks_v4 = coalesce(of visit_3 - visit_1);
		lks_v5 = coalesce(of visit_4 - visit_1);
	end;
	
run;