DATA Step, Macro, Functions and more

Fill the blank with the next non blank observation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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,023

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;

View solution in original post


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: 7,771

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
Solution
‎11-24-2016 01:05 AM
Super User
Posts: 10,023

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 212 views
  • 2 likes
  • 3 in conversation