BookmarkSubscribeRSS Feed
George_S
Fluorite | Level 6

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

data ask;

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

run;

9 REPLIES 9
MikeZdeb
Rhodochrosite | Level 12

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__
Jade | Level 19

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
Rhodochrosite | Level 12

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
Opal | Level 21

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__
Jade | Level 19

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
Rhodochrosite | Level 12

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__
Jade | Level 19

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
Super User

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
Obsidian | Level 7

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).

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 26453 views
  • 8 likes
  • 6 in conversation