How to rearrange the columns' order alphabetically

[George_S]

Is there any better way to rearrange the variables in alphabetically order?

data ask;

a_555=1;c_666=3;b_111=2;

run;

[MikeZdeb]

hi ... one idea ...

data old;
a_555=1;
c_666=3;
b_111=2;
run;

proc sql noprint;
select name into :vars separated by ' '
from dictionary.columns
where libname eq 'WORK' and memname eq 'OLD'
order by name;
quit;

data old;
retain &vars;
set old;
run;

[data_null__]

If there are enumerated list variables a1-a100 for example I don't think this will order the variables in a pleasing way.  There is a PROC SORT option that I can't think of right now that would work but I don't know how or if that can be implement in SQL orderby.

PROC CONTENS may output the names in the proper order by default I can't remember.  There is ORDER= option. 

[MikeZdeb]

hi ... correct (as usual), so (I don't know how to use the different sort options within SQL) ...

data old;
z2=10; a_555=1; c_666=3; z1=9; b_111=2; z100=10;

run;

proc sql noprint;
create table vars as
select name from dictionary.columns
where libname eq 'WORK' and memname eq 'OLD';
quit;

proc sort data=vars sortseq=linguistic(numeric_collation=on);
by name;
run;

proc sql noprint;
select name into :vars separated by ' ' from vars;

drop table vars;
quit;

data old;
retain &vars;
set old;
run;     

[art297]

DN: I think you are referring to numeric_collation

proc sort data=one out=two

sortseq=linguistic (numeric_collation=on);

by bank;

run;

but I don't think it is avaiable in proc sql.  However, Chang Chung proposed some code in a thread a couple of years ago:

This sounds like a reasonable approach. Below surely is. IMHO, that is.

Re: Order variables in dataset

   /* test data */

   data one;

     retain a1 a10a a11 a1a a2 .;

   run;

   /* prepare an ordered list of vars */

   %let num = 0123456789;

   %let ordered=;

   proc sql noprint;

     select   name into :ordered separated by " "

     from     dictionary.columns

     where    libname="WORK" and memname="ONE"

     order by scan(name, 1, "&num")

            , inputn(scan(name, 1, "&num", "k"), "best")

            , scan(name, 2, "&num");

   quit;

   /* re-order */

   data two;

     retain &ordered;

     set one;

   run;

   /* check */

   proc contents data=two order=varnum;

   run;

   /* on lst -- in part

1.    Variable    Type    Len

   1    a1          Num       8

   2    a1a         Num       8

   3    a2          Num       8

   4    a10a        Num       8

   5    a11         Num       8

   */

[data_null__]

The default out= from PROC CONTENTS or perhaps ORDER=IGNORECASE is  probably the most pleasing order with regards to enumerated lists.  No name fiddling required.

[MikeZdeb]

hi ... hmmm, I think that depends ...

if you want all the q_2 vars listed between q_1 and q_10 ...

data old;
retain q_1 q_34 q_2a  q_2 q_2x q_10 0;
run;

CONTENTS doesn't work, but NUMERIC COLLATION does

I guess one has to make a choice    

[data_null__]

proc contents treats enumerated variables in a "special way" even if some of them are not present.  Variables that end in characters are not enumerated variables.

Like you say depends on what you want.

[Ksharp]

How about:

This solution  is from Howles.

data old;
z2=10; a_555=1; c_666=3; z1=9; b_111=2; z100=10;
run;

 

proc sql noprint;
select name into : list separated by ' ' from dictionary.columns
 where libname eq 'WORK' and memname eq 'OLD'
  order by input(compress(name, ,'kd'),best8.);
quit;
%put &list;

Ksharp

[OS2Rules]

You may want to hhave a look at this trhead - I needed a similar process for an FSVIEW ..

https://communities.sas.com/message/102658#102658

(works brilliantly BTW).