BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
niam
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ScottBass
Rhodochrosite | Level 12

Wow, this is sweet Smiley Wink  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.


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

View solution in original post

10 REPLIES 10
ballardw
Super User

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
Quartz | Level 8

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

Thank you very much, It worked!

Ksharp
Super User
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
Onyx | Level 15

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
Quartz | Level 8

thank you;

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

data_null__
Jade | Level 19

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
Rhodochrosite | Level 12

Wow, this is sweet Smiley Wink  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.


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
data_null__
Jade | Level 19

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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1416 views
  • 7 likes
  • 7 in conversation