Retain within group with condition

[hhchenfx]

Hi Everyone,

My data has ID, Key_Value, v1,v2,v3.

In the data, when Key_value ^=. , at least 1 of v1,v2,v3 will have value.

I want to fill all missing value of v1, v2, v3 with the corresponding value associated with the most recent available Key_value.

The tricky part is that retain will fill value all the way until it reach non-missing value of the same column (instead of the Key_value column).

The first few rows desirable output should look like where for V2 =3 only fills up to 3rd record.

Can you please help?

Many thanks,

HHC

1 . . . .
1 6 5 3 .
1 . 5 3 .
1 7 8 . ./*No fill of value 3 here as Key_value contain non-missing*/
1 . 8 . .

Input file

data have;
  input id key_value v1 v2 v3;
  datalines;
1 . . . .
1 6 5 3 .
1 . . . .
1 7 8 . .
1 . . . .
2 9 . . 9
2 . . . .
2 1 . 7 .
2 . . . .
;

[A_Kh]

How about this code? 

data want (rename= (_v1=v1 _v2=v2 _v3=v3));
	set have;
	by id; 
	retain _v1 _v2 _v3;
	array A [3] v1-v3;
	array B [3] _v1-_v3;
	do i= 1 to 3;
		if key_value^=.  then B{i}=A{i};
	end;
	drop i v:;
proc print; run; 

[Tom · Accepted solution]

I am having a very hard time trying to figure out why doing something like this makes any sense.

But this method works for your first ID that you showed example output for.

You can use UPDATE statement to do last observation carried forward.

Then you should re-read the value of KEY_VALUE so it is not carried forward.  And when KEY_VALUE is not empty then re-read the whole observation so none of the variables are carried forward.  Also add an explicit OUTPUT statement so you get back out the same number of observations instead of just one per ID value.

data have;
  input id key_value v1 v2 v3;
datalines;
1 . . . .
1 6 5 3 .
1 . . . .
1 7 8 . .
1 . . . .
2 9 . . 9
2 . . . .
2 1 . 7 .
2 . . . .
;

data want;
  update have(obs=0) have;
  by id;
  set have(keep=key_value);
  if not missing(key_value) then set have point=_n_;
  output;
run;

Results

              key_
Obs    id    value    v1    v2    v3

 1      1      .       .     .     .
 2      1      6       5     3     .
 3      1      .       5     3     .
 4      1      7       8     .     .
 5      1      .       8     .     .
 6      2      9       .     .     9
 7      2      .       .     .     9
 8      2      1       .     7     .
 9      2      .       .     7     .

[hhchenfx]

Thanks, Tom!

Your response makes my day 🙂

HHC

[Ksharp]

data have;
  input id key_value v1 v2 v3;
  datalines;
1 . . . .
1 6 5 3 .
1 . . . .
1 7 8 . .
1 . . . .
2 9 . . 9
2 . . . .
2 1 . 7 .
2 . . . .
;

data temp;
 set have;
 by id;
 if first.id or not missing(key_value) then group+1;
 keep id group v:;
run;
data temp2;
 update temp(obs=0) temp;
 by id group;
 output;
run;
data want;
 merge have(keep=id key_value) temp2(keep=v:);
run;