topic Fill the blank with the next non blank observation in SAS Programming

Fill the blank with the next non blank observation

JT99 — Wed, 23 Nov 2016 05:46:47 GMT

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.

Re: Fill the blank with the next non blank observation

JT99 — Wed, 23 Nov 2016 05:50:07 GMT

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.

Re: Fill the blank with the next non blank observation

Kurt_Bremser — Wed, 23 Nov 2016 11:34:40 GMT

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.

Re: Fill the blank with the next non blank observation

Ksharp — Wed, 23 Nov 2016 11:17:19 GMT

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;

Re: Fill the blank with the next non blank observation

JT99 — Thu, 24 Nov 2016 06:06:12 GMT

Thank you both!