topic Re: Retain within group with condition in SAS Programming

Retain within group with condition

hhchenfx — Thu, 09 Nov 2023 21:43:24 GMT

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 . . . .
;

Re: Retain within group with condition

A_Kh — Thu, 09 Nov 2023 22:23:18 GMT

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;

Re: Retain within group with condition

Tom — Thu, 09 Nov 2023 23:28:56 GMT

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     .

Re: Retain within group with condition

Ksharp — Fri, 10 Nov 2023 01:17:39 GMT

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;

Re: Retain within group with condition

hhchenfx — Fri, 10 Nov 2023 02:45:56 GMT

Thanks, Tom!

Your response makes my day 🙂

HHC