Help using Base SAS procedures

Shifting data in column to previous column when blank

Reply
Contributor
Posts: 41

Shifting data in column to previous column when blank

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.

Super User
Posts: 11,343

Re: Shifting data in column to previous column when blank

Posted in reply to carmendee

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?

Contributor
Posts: 41

Re: Shifting data in column to previous column when blank

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

SAS Super FREQ
Posts: 708

Re: Shifting data in column to previous column when blank

Posted in reply to carmendee

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

;

Respected Advisor
Posts: 3,156

Re: Shifting data in column to previous column when blank

Posted in reply to Bruno_SAS

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

Super User
Posts: 11,343

Re: Shifting data in column to previous column when blank

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

Respected Advisor
Posts: 3,156

Re: Shifting data in column to previous column when blank

Figured. Smiley Wink

Contributor
Posts: 41

Re: Shifting data in column to previous column when blank

Posted in reply to Bruno_SAS

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

Trusted Advisor
Posts: 1,228

Re: Shifting data in column to previous column when blank

Posted in reply to carmendee

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;

Respected Advisor
Posts: 3,156

Re: Shifting data in column to previous column when blank

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

Super User
Posts: 11,343

Re: Shifting data in column to previous column when blank

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.

Contributor
Posts: 41

Re: Shifting data in column to previous column when blank

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.

Respected Advisor
Posts: 3,156

Re: Shifting data in column to previous column when blank

Posted in reply to carmendee

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

Super User
Posts: 11,343

Re: Shifting data in column to previous column when blank

And handles multiples easily. I like it!

Super User
Posts: 10,018

Re: Shifting data in column to previous column when blank

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

Ask a Question
Discussion stats
  • 14 replies
  • 371 views
  • 0 likes
  • 6 in conversation