topic Re: Replace missing value with previous value in SAS Procedures

Replace missing value with previous value

Belle — Fri, 17 Jul 2015 02:28:50 GMT

Hello,

I want to get each id with non-missing y and z. but if I have highest x with non-missing y or z, then I will keep them.

My approach is if I can replace missing x and y to the previous no-missing value, then at the end, I can just keep last.id. However, I'm having difficulty to replace these missing value. I try lag, but doesn't work well.

I appreciate any help.

data temp;

input id x y z;

cards;

1 2 3 2

1 4 5 .

1 4 . 4

1 4 . 7

2 6 4 2

2 6 8 .

2 7 . 8

2 7 9 .

2 9 . .

3 4 2 3

3 4 4 .

3 7 . 9

3 7 . 6

4 2 . 1

4 3 4 2

;

run;

The result I expect is:

id x y z;

1 4 5 7

2 7 9 8

3 7 4 6

4 3 4 2

Re: Replace missing value with previous value

slchen — Fri, 17 Jul 2015 03:17:43 GMT

proc sql;

select id, max(x) as x,max(y) as y,max(z) as z from temp group by id;

quit;

Re: Replace missing value with previous value

HarryLiu — Fri, 17 Jul 2015 04:26:31 GMT

Hi Belle,

To replace missing value, you can use retain statement.

The sample code is as following:

data want;

set temp;

retain new_y;

if missing (y) then new_y=x;

else new_y=y;

output;

run;

proc print;

run;

Best,

Harry Liu

Re: Replace missing value with previous value

stat_sas — Fri, 17 Jul 2015 04:31:05 GMT

data want(drop=i a:);

set temp;

by id;

retain a1-a3;

array t(*) a1-a3;

array v(*) x y z;

do i=1 to dim(v);

if v(i) ne . then t(i)=v(i);

v(i)=t(i);

end;

if last.id;

run;

Re: Replace missing value with previous value

Belle — Fri, 17 Jul 2015 04:36:03 GMT

sIchen -- Thanks for your reply. I'm not look for the max value on each column. I am looking for the non-missing value for y and z that has the latest x.

Re: Replace missing value with previous value

HarryLiu — Fri, 17 Jul 2015 04:56:45 GMT

Dear Belle,

You can try this code:

data have;

set temp;

if y ne . and z ne . then output;

run; *build a new data set with non-missing of y an z.*

proc sql;

select id max(x) as x, y, z *select the highest x from the data set*

from have

group by id;

quit;

proc print;

run;

Re: Replace missing value with previous value

ChrisNZ — Fri, 17 Jul 2015 10:01:09 GMT

data HAVE;

input ID X Y Z;

cards;

1 2 3 2

1 4 5 .

1 4 . 4

1 4 . 7

2 6 4 2

2 6 8 .

2 7 . 8

2 7 9 .

2 9 . .

3 4 2 3

3 4 4 .

3 7 . 9

3 7 . 6

4 2 . 1

4 3 4 2

run;

data WANT;

merge HAVE(keep=ID X Y where=(Y ne .))

HAVE(keep=ID X Z where=(Z ne .));

by ID ;

if last.ID;

putlog ID X Y Z;

run;

1 4 5 7

2 7 9 8

3 7 4 6

4 3 4 2

Re: Replace missing value with previous value

viveklanka — Fri, 17 Jul 2015 11:21:35 GMT

You can do this way:

data want;

set have;

retain Y_new;

by ID;

if ID=lag(ID) then do;

if Y=. then Y=y_new;

else y_new=y;

end;

if last.ID then output want;

run;

Re: Replace missing value with previous value

Ksharp — Fri, 17 Jul 2015 13:40:25 GMT

Code: Program

data HAVE;
   input ID X Y Z;
   cards;
  1 2 3 2
  1 4 5 .
  1 4 . 4
  1 4 . 7
  2 6 4 2
  2 6 8 .
  2 7 . 8
  2 7 9 .
  2 9 . .
  3 4 2 3
  3 4 4 .
  3 7 . 9
  3 7 . 6
  4 2 . 1
  4 3 4 2
run;
data want;
 set have;
 by id;
 retain _x _y _z;
 if first.id then call missing(_x,_y,_z);
 if not missing(x) then _x=x;
 if not missing(y) then _y=y;
 if not missing(z) then _z=z;
 if last.id;
 drop x y z;
run;

Re: Replace missing value with previous value

Belle — Fri, 17 Jul 2015 16:55:55 GMT

Thanks stat@sas, your answer is pretty close, except for id2, x should be 7 instead of 9. if I delete missing y and z at the beginning, then it works.

Re: Replace missing value with previous value

Belle — Fri, 17 Jul 2015 16:59:20 GMT

HarryLiu,

I appreciate your help, I am not trying to get no missing fields. I want to replace these missing fields with previous value, so I can get the last.id information. But your information may help my future cases.

Re: Replace missing value with previous value

Belle — Fri, 17 Jul 2015 17:00:10 GMT

Thanks Chris, your answer works well.

Re: Replace missing value with previous value

Belle — Fri, 17 Jul 2015 17:05:39 GMT

Hi xia keshan,

Thanks for your helpful answer. your answer is same as stat@sas, problem on id2. But I can fix it by eliminate missing y and z first.

Re: Replace missing value with previous value

Belle — Fri, 17 Jul 2015 17:06:55 GMT

Thanks you all again. All the information are helpful. I learn so much from your all.

Re: Replace missing value with previous value

Belle — Fri, 17 Jul 2015 17:11:01 GMT

viveklanka, thanks for you help.

Re: Replace missing value with previous value

ChrisNZ — Sun, 19 Jul 2015 06:32:43 GMT

No worries. It s the first time I see SAS's merge behaviour with multiple keys actually be useful!

Re: Replace missing value with previous value

Astounding — Mon, 20 Jul 2015 22:45:40 GMT

SAS does contain a tool that may do exactly what you want. Assuming your data are sorted:

data want;

update have (obs=0) have;

by id;

run;

That way, you can automatically expand this to many variables instead of just x, y, and z.

Good luck.

Re: Replace missing value with previous value

anan — Tue, 21 Jul 2015 11:58:17 GMT

Just another way

data want;

set have;

retain X_new Y_new Z_new ;

by ID;

array now(3) X Y Z;

array three(3)X_new Y_new Z_new ;

do i=1 to 3;

if ID=lag(ID) then do;

if now(i)^=. then three(i)=now(i);

end;

if last.ID then output want;

drop x y z;

run;