Shifting data in column to previous column when blank

[carmendee]

I have 9 columns of data.  If column 1 is empty, I want to move the next column to column 1.

Example:

Record 1:

col1     col2     col3

blank     beans rice

Record 2:

col1     col2     col3

beets     blank     lettuce

Desired result:

Record 1 has col1 and col2 populated with beans and rice

Record 2 has col1 and col2 2 populated with beans and lettuce

Thanks for ideas with the quickest approach.

[ballardw]

Does the actual order matter?

And when you say blank do you mean that the value is a zero length string or a string containing only blank characters?

[carmendee]

A string (character) that is blank.  Order does not matter.

[BrunoMueller]

You could use an array for the variables, sort the array and you are set. As with sorting the missing value comes first I changed the order of the variables in the array.

See code sample below

data have;

  infile cards dlm="," dsd;

  input

    col1 : $12.

    col2 : $12.

    col3 : $12.

  ;

  array xcol{*} col3 col2 col1 ;

  putlog "NOTE: Before Sort: " xcol{*}=;

  call sortc( of xcol{*} );

  putlog "NOTE: After Sort: " xcol{*}=;

cards;

,beans,rice

beets,,lettuce

;

[Haikuo]

Like it, since OP stated "Order does not matter.".

[ballardw]

I asked about order as I was thinking about a SORTC based solution.

[Haikuo]

Figured.

[carmendee]

There will not be duplicates in columns for the row.  I will try this!  Thanks.

[stat_sas]

data want(drop=i);

set have;

array c(*) col1-col3;

do i=1 to dim(c);

if c{i}=' ' then c{i}=c{i+1};

end;

run;

[Haikuo]

I can't see anywhere in OP's request that  duplicates are desired.

[ballardw]

Need a nested loop to handle more than one blank and to move the blank to the rightmost position(s) and upper limit on the array would be dim(c) -1 to avoid out of bounds errors. Also you want to swap values else the result could result in duplications.

blank  banana orange

would become

banana banana orange

with suggested code.

[carmendee]

I see. Well, I could have a blank in the first column but not in the other 8 columns, so i would want to shift every column to the left by one column starting with the blank column.

[Haikuo]

If you do want 'SHIFTING' without changing the order, here is a simple alternative:

data have;

     infile cards dlm="," dsd;

     input

           col1 : $12.

           col2 : $12.

           col3 : $12.

     ;

     cards;

,rice,beans

,beans,rice

beets,,lettuce

;

data want;

set have;

array xcol col1-col3;

     length cat $ 50;

     cat=catx(',', of xcol(*));

     do over xcol;

           xcol=scan(cat,_i_);

     end;

     keep col:;

run;

Haikuo

[ballardw]

And handles multiples easily. I like it!

[Ksharp]

What if COL also contains comma separator ?

data have;
     infile cards dlm="," dsd;
     input
           col1 : $12.
           col2 : $12.
           col3 : $12.
     ;
     cards;
,rice,beans
,beans,rice
beets,,lettuce
;
run;
data want;
 set have;
 array x{9999} $ 50 _temporary_;
 array c{*} $ col: ;
 n=0;
 do i=1 to dim(c);
  if not missing(c{i}) then do;n+1;x{n}=c{i};end;
 end;
 do i=1 to dim(c);
  c{i}=x{i};
 end;
 call missing(of x{*});
 drop i n;
run;

Xia Keshan