topic Re: How to replace only the first missing numeric value from a column? in SAS Programming

How to replace only the first missing numeric value from a column?

Saurabh_Rana — Thu, 17 Dec 2020 08:13:36 GMT

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.

Re: How to replace only the first missing numeric value from a column?

PeterClemmensen — Thu, 17 Dec 2020 08:17:57 GMT

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:

Re: How to replace only the first missing numeric value from a column?

s_lassen — Thu, 17 Dec 2020 11:03:05 GMT

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.