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.
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?
A string (character) that is blank. Order does not matter.
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
;
Like it, since OP stated "Order does not matter.".
I asked about order as I was thinking about a SORTC based solution.
Figured.
There will not be duplicates in columns for the row. I will try this! Thanks.
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;
I can't see anywhere in OP's request that duplicates are desired.
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.
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.
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
And handles multiples easily. I like it!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: