I have a column in a table like this -
Column A
1
2
6
.
.
.
Now I only want to replace the first missing value in the column with a numeric value(For ex -10)
Output
Column A
1
2
6
10
.
.
The code should be dynamic, i.e. It should automatically identify the first missing value and then replace it.
Try this
data have;
input a;
datalines;
1
2
6
.
.
.
;
data want;
set have;
if a = . & _iorc_ = 0 then do;
a = -10;
_iorc_ = 1;
end;
run;
Result:
a 1 2 6 -10 . .
Try this
data have;
input a;
datalines;
1
2
6
.
.
.
;
data want;
set have;
if a = . & _iorc_ = 0 then do;
a = -10;
_iorc_ = 1;
end;
run;
Result:
a 1 2 6 -10 . .
I would use this construct:
data want;
set have;
by a notsorted;
if first.a and missing(a) then
a=10;
run;
It is easier to understand than manipulating _IORC_, as suggested by @PeterClemmensen
EDIT: But it does work differently; it replaces the first of any series of missing values, not just the very first missing value.
So, with you example data, it gives the same result. But with data like this:
data have;
input a;
datalines;
1
2
6
.
.
.
5
.
.
;run;
it gives a different result, as the missing value after 5 is also replaced.
If you only want the first missing value replaced (not the first in each series), another possibility is to modify the data in place, which is probably the fastest, CPU-wise:
data have;
modify have;
if missing(a) then do;
a=10;
replace;
stop;
end;
run;
Meaning that you do not read and copy the 3 zillion observations after the first missing value.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.