## Fill the blank with the next non blank observation

Solved
Occasional Contributor
Posts: 15

# Fill the blank with the next non blank observation

Hello! My data looks like this:
Name Col1 Col2 Col3 Col4
ABC 1245 7890 1290
XRT 5634 2384 1276
UTR 6734 5689 6365 1251

Is there a way to move the next nonmissing value to the blank space per row?
Also, I want the number with the first 2 digits="12" to be on col1.
The first row should look like this:
ABC 1245 1290 7890

Any help will be greatly appreciated.

Accepted Solutions
Solution
‎11-24-2016 01:05 AM
Super User
Posts: 10,846

## Re: Fill the blank with the next non blank observation

```It is numeric or character type variable ?

data have;
infile cards truncover;
input Name \$ Col1 Col2 Col3 Col4;
cards;
ABC 1245 7890  . 1290
XRT 5634 2384 1276
UTR 6734 5689 6365 1251
;
run;
data want;
set have;
array x{*} col1-col4;
array y{*} new1-new4;
call sortn(of x{*});
n=0;
do i=1 to dim(x);
if not missing(x{i}) then do;n+1;y{n}=x{i};end;
end;
drop n i col:;
run;

```

All Replies
Occasional Contributor
Posts: 15

## Re: Fill the blank with the next non blank observation

I notice that the data now looks different when I posted it. The blank spaces were gone. What I meant was if col3 is blank in a row,i want the value in col4 to be transferred to col3.
Super User
Posts: 10,530

## Re: Fill the blank with the next non blank observation

[ Edited ]
``````data want;
set have;
array cols {*} col1-col4;
do i = dim(cols) - 1 to 1 by -1;
if cols{i} = . and cols{i+1} ne . then cols{i} = cols{i+1};
end;
drop i;
run;``````

PS if col1 to colX are of type character, use ' ' instead of the dot for a missing value.

Edit: added "by -1" in the do statement.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎11-24-2016 01:05 AM
Super User
Posts: 10,846

## Re: Fill the blank with the next non blank observation

```It is numeric or character type variable ?

data have;
infile cards truncover;
input Name \$ Col1 Col2 Col3 Col4;
cards;
ABC 1245 7890  . 1290
XRT 5634 2384 1276
UTR 6734 5689 6365 1251
;
run;
data want;
set have;
array x{*} col1-col4;
array y{*} new1-new4;
call sortn(of x{*});
n=0;
do i=1 to dim(x);
if not missing(x{i}) then do;n+1;y{n}=x{i};end;
end;
drop n i col:;
run;

```
Occasional Contributor
Posts: 15

## Re: Fill the blank with the next non blank observation

Thank you both!
☑ This topic is solved.