Date:         Mon, 10 Feb 2003 21:46:36 -0500
Reply-To:     lpogoda <lpogodajr292185@COMCAST.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         lpogoda <lpogodajr292185@COMCAST.NET>
Subject:      Re: Re-ordering the positions of columns

Ed Heaton wrote in message
<9B501B3774931C469BCCCC021BE5372231E5C0@remailnt2-re01.westat.com>...
>Glen, try this
>
>DATA test ;
>   Array x [*] Ed Nazik Bob Alla Ethel Bernadette ;
>   Stop ;
>Run ;
>
>Proc contents data=test out=vars(keep=name) noPrint ;
>Run ;
>
>Proc sql noPrint ;
>   Select
>      name into :orderedVars separated by " "
>      from vars
>      order by upCase(name)
>   ;
>   Drop vars ;
>Quit ;
>
>Data new ;
>   Retain &orderedVars ;
>   Set test ;
>Run ;
>

In version 8, I've run into at least one case where using RETAIN this way
does not reorder the variables in the resulting dataset.  Since you're in
PROC SQL anyway you might just as well use the dictionary tables to get the
names of the variables in the first place and then continue on and use it to
rewrite your dataset instead of using a DATA step, as in this tested code:

proc sql noprint ;
      select  name into :orderedvars separated by ", "
      from dictionary.columns
      where lowcase(memname) = 'test'
      order by lowcase(name) ;
   create table new as
      select &orderedvars
      from test;
quit;

But however it's done, I don't think it's very efficient to rewrite a
dataset simply to reorder the variables, which is generally not required
from a processing standpoint.  If you simply must provide an ordered dataset
for non-programming types to look at, you might consider providing a view.
In that case, the above code would become:

proc sql noprint ;
      select  name into :orderedvars separated by ", "
      from dictionary.columns
      where lowcase(memname) = 'test'
      order by lowcase(name) ;
   create view new as
      select &orderedvars
      from test;
quit;