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?
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.
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;
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;
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;
Thank you very much, It worked!
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
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;
thank you;
this one actually works for all types of data, the answer by Linlin does not work with text data.
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;
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.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: