Date:         Wed, 17 Jul 1996 10:48:38 EDT
Reply-To:     whitloi1@WESTATPO.WESTAT.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Ian Whitlock <whitloi1@WESTATPO.WESTAT.COM>
Subject:      Re: Q: How to rearrange variables?

    Subject: Q: How to rearrange variables?
    Summary: Macro to get alpha-numeric order (slightly improved from
             previous SAS-L post).
    Respondent: Ian Whitlock <whitloi1@westat.com>
 
    Dmitry Mesyanzhinov <dmes@ec26.enrg.lsu.edu> writes:
 
    >When I run proc means or contents on my dataset,
    >variables appear in the order in which they were created.
    >There are about 300 variables in the dataset,
    >and it would be more convinient if the listing is
    >done in, say, alphabetical order. I tried to specify
    >the desired sequence of variables in the KEEP option,
    >but that didn't work.
    >Is there a way to rearrange the variables in a dataset?
 
    You can reorder variables with a RETAIN statement (no value at the
    end) at the top of the DATA step.  On the other hand if you want to get
    alpha-numeric order in a PROC it is simple in 6.11 (6.09e) to make a
    macro variable giving the order and then then use it in a VAR statement
    in the PROC.  Which is best?  It depends on how many times you want
    alphanumeric order, how large the data set is, and  how easily the
    variables get out of order.  With a large enough data set is may take
    many PROC calls to make it worth passing the data just to change the
    order of variables.  A macro ALPHANUM makes it simple to capture the
    desired order.  Here is some test code.
 
          /* test data not vars not in alpha order             */
          data w ; retain y10 y2 x z a c b 1 ; run ;
 
          /* initialization only needed when macvar ^= alpha   */
          %let alpha = ?;             /* I did it anyway       */
          %alphanum ( data = w )      /* get alphnumeric order */
 
          proc means data = w ;
             var &alpha ;        /* use the alphanumeric order */
          run ;
 
          /* to rearrange the data */
          data w ;
             retain &alpha ;
             set w ;
          run ;
 
    Here is the macro.
 
    %macro alphanum ( data = _LAST_ , macvar = ALPHA , type = ) ;
       %* obtain list of variables in alphanumeric order in macro ;
       %* variable named by parameter MACVAR ;
 
       %local lib mem ;
       %let data = %upcase ( &data ) ;
       %if &data = _LAST_ %then %let data = &syslast ;
       %if %index ( &data , . ) = 0 %then %let data = WORK.&data ;
       %let lib = %qscan ( &data , 1 , . ) ;
       %let mem = %qscan ( &data , 2 , . ) ;
       %if %upcase ( &macvar ) = ALPHA %then %global alpha ;
 
       proc sql ;
         reset noprint ;
         create view __v as
         select
           name ,
           left(reverse(name)) as q ,
           verify ( calculated q , ' 0123456789' ) as x ,
           left(reverse(substr(calculated q,calculated x))) as n1 ,
           case
             when calculated x > 1 then
               input(left(reverse(substr(calculated q,1,calculated x - 1))),8.)
             else 0
           end as n2
           from dictionary.columns
           where libname = "&lib" and memname = "&mem"
                 %if &type = 1  %then and type = "num" ;
                 %if &type = 2  %then and type = "char" ;
           order by n1 , n2  /* n1=character part, n2=numeric part of name */
         ;
         select name into :&macvar separated by ' ' from __v ;
      quit ;
 
    %mend  alphanum ;
 
    If one doesn't have access to 6.11 or 6.09e then drop the second SELECT
    statement from the PROC SQL and add DATA step code to create an array
    of macro variables giving the data variable names.
 
    Ian Whitlock