topic Strange Case of Missing Values in SAS Programming

Strange Case of Missing Values

niam — Mon, 14 May 2012 21:47:24 GMT

Hello Everyone

I have a small problem which I think can be solved by loops in SAS data steps,I appreciate if you help me with that:

I have a data set in which if a value is missing then it shall be assigned the nearest non missing value in the previous observations, for example, 6 rows of my data looks like this:

I want to assign the missing values so that it would read like this

Can SAS do that for me?

Re: Strange Case of Missing Values

ballardw — Mon, 14 May 2012 22:14:42 GMT

What will you do if the first value is missing?

RETAIN is likely your easiest option.

Data new;

set old;

Retain testvalue .;

If value ne . then testvalue = value;

if value = . then value = testvalue;

run;

Re: Strange Case of Missing Values

niam — Mon, 14 May 2012 22:24:09 GMT

Thank you very much for the super fast reply, I am afraid I am not sure if I have got your idea correctly, this is how I implemented it, but the results are not good, by the way, the first value in my data set is never missing.

data test;

input x;

datalines;

;

run;

Data new;

set test;

Retain x .;

If x ne . then x = value;

if x = . then value = x;

run;

Re: Strange Case of Missing Values

Linlin — Mon, 14 May 2012 23:53:17 GMT

try:

data test;

input x;

datalines;

;

run;

Data new (drop=value);

set test;

retain value;

value=ifn(x=.,value,x);

x=coalesce(x,value);

run;

proc print;run;

Re: Strange Case of Missing Values

niam — Tue, 15 May 2012 02:47:53 GMT

Thank you very much, It worked!

Re: Strange Case of Missing Values

Ksharp — Tue, 15 May 2012 07:03:36 GMT

data test;
input x;
datalines;
1
.
.
2
.
3
;
run;

Data new;
     set test;
     Retain xx ;
     if not missing(x) then xx=x;
     drop x;
run;

Ksharp

Re: Strange Case of Missing Values

Haikuo — Tue, 15 May 2012 16:15:24 GMT

Or:

data have;

input a;

cards;

;

data want (drop=_:);

retain _temp;

set have;

a=coalesce(a,_temp);

output;

_temp=a;

run;

Haikuo

Update: or use DOW to retain:

data want (drop=_:);

do until (last);

set have end=last;

a=coalesce(a,_temp);

output;

_temp=a;

end;

run;

Re: Strange Case of Missing Values

niam — Sun, 15 Jul 2012 22:30:39 GMT

thank you;

this one actually works for all types of data, the answer by Linlin does not work with text data.

Re: Strange Case of Missing Values

data_null__ — Mon, 16 Jul 2012 01:39:36 GMT

data try;

set sashelp.class;

retain new +1;

if _n_ in(2,3,4,5,8,9,12,16,17,18)

then call missing (of name age weight);

else call missing(of sex height);

run;

proc print;

run;

data step;

update try(obs=0) try;

by new;

output;

run;

proc print;

run;

Re: Strange Case of Missing Values

ScottBass — Mon, 16 Jul 2012 02:11:06 GMT

Wow, this is sweet I've seen a lot of approaches to LOCF, but never this one. I Googled "SAS LOCF", but stopped after the 3rd hit - none of them used the update statement.

To the original poster, Google "SAS LOCF" for more approaches (or you could just quit with Mr. _null_'s approach!)

A tiny, minuscule point: if your source dataset was huge (like millions of records), you could also use a view:

data try;

set sashelp.class;

if _n_ in(2,3,4,5,8,9,12,16,17,18)

then call missing (of name age weight);

else call missing(of sex height);

run;

proc print;

run;

data try2 / view=try2;

set try;

new=1;

run;

data step;

update try2(obs=0) try2;

by new;

output;

drop new;

run;

proc print;

run;

Message was edited by: Scott Bass Hey Niam, thanks for the credit for the correct answer, but the real credit goes to Mr. _null_. I just stole his code and added some syntax highlighting 🙂 But hey, he already has enough points lol.

Re: Strange Case of Missing Values

data_null__ — Mon, 16 Jul 2012 10:28:14 GMT

Thanks Scott.

You could also have real BY variables like USUBJID PARAMCD etc.

data try;

set sashelp.class;

_obs_ = _n_;

if _n_ in(1,3,4,5,8,9,12,16,17,18)

then call missing (of name age weight);

run;

proc sort data=try;

by sex;

run;

proc print;

run;

data step;

update try(obs=0) try;

by sex;

output;

run;

proc print;

run;