Strange Case of Missing Values

[niam]

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:

1

.

.

2

.

4

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

1

1

1

2

2

4

Can SAS do that for me?

[ballardw]

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;

[niam]

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;

1

.

.

2

.

3

;

run;

Data new;

     set test;

     Retain x .;

     If x ne . then x = value;

     if x = . then value = x;

run;

[Linlin]

try:

data test;

input x;

datalines;

1

.

.

2

.

3

;

run;

Data new (drop=value);

     set test;

  retain value;

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

     x=coalesce(x,value);

run;

proc print;run;

[niam]

Thank you very much, It worked!

[Ksharp]

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

[Haikuo]

Or:

data have;

input a;

cards;

1

.

.

2

.

4

;

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;

[niam]

thank you;

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

[data_null__]

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;

[ScottBass · Accepted solution]

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.

[data_null__]

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;