BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JT99
Obsidian | Level 7
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.


1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

4 REPLIES 4
JT99
Obsidian | Level 7
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.
Kurt_Bremser
Super User
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.

Ksharp
Super User
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;

JT99
Obsidian | Level 7
Thank you both!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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