topic Re: Shifting data in column to previous column when blank in SAS Procedures

Shifting data in column to previous column when blank

carmendee — Thu, 13 Nov 2014 18:39:09 GMT

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.

Re: Shifting data in column to previous column when blank

ballardw — Thu, 13 Nov 2014 19:07:56 GMT

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?

Re: Shifting data in column to previous column when blank

carmendee — Thu, 13 Nov 2014 19:11:03 GMT

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

Re: Shifting data in column to previous column when blank

BrunoMueller — Thu, 13 Nov 2014 19:47:28 GMT

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

;

Re: Shifting data in column to previous column when blank

stat_sas — Thu, 13 Nov 2014 19:54:38 GMT

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;

Re: Shifting data in column to previous column when blank

Haikuo — Thu, 13 Nov 2014 20:01:32 GMT

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

Re: Shifting data in column to previous column when blank

Haikuo — Thu, 13 Nov 2014 20:03:03 GMT

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

Re: Shifting data in column to previous column when blank

carmendee — Thu, 13 Nov 2014 20:05:48 GMT

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

Re: Shifting data in column to previous column when blank

ballardw — Thu, 13 Nov 2014 20:49:29 GMT

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

Re: Shifting data in column to previous column when blank

ballardw — Thu, 13 Nov 2014 20:54:04 GMT

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.

Re: Shifting data in column to previous column when blank

carmendee — Thu, 13 Nov 2014 20:58:25 GMT

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.

Re: Shifting data in column to previous column when blank

Haikuo — Thu, 13 Nov 2014 21:47:16 GMT

Figured.

Re: Shifting data in column to previous column when blank

Haikuo — Thu, 13 Nov 2014 22:22:13 GMT

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

Re: Shifting data in column to previous column when blank

ballardw — Thu, 13 Nov 2014 22:59:37 GMT

And handles multiples easily. I like it!

Re: Shifting data in column to previous column when blank

Ksharp — Fri, 14 Nov 2014 11:38:57 GMT

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