Sorting Variable Names Alphabetically

How to change the order of variables within a dataset is one of the most frequently asked questions in the SAS world. The standard answer is to use the LENGTH statement (before any SET statement) to list the variables in the desired order.  Here is a technique for achieving something slightly different: sorting the variables into alphabetical order:

Program:

proc sql noprint;
  select name into :varlist separated by ',' from
  dictionary.columns
  where libname='SASHELP' and memname='CLASS'
  order by name;
  create table class as
  select &varlist from sashelp.class;
quit;

proc print data=sashelp.class(obs=5) noobs;
proc print data=class(obs=5) noobs;
run;

The list of variables present in the data set is looked up in the SQL dictionary tables (which are equivalent to the SASHELP VIEWS). The names of the library and member have to be specified in upper case. The returned list of variable names is saved into a macro variable &VARLIST. We can then use this immediately within the same invocation of SQL. (This is different from the situation with data steps, where a macro variable cannot be both created and used within the same step). A second SQL command creates a temporary copy of the dataset with the variables in the desired order (we could overwrite the existing data set if required). Proc PRINT steps are included in order to show the effect:

Output:

SASHELP.CLASS (shows original order of variables)

Name      Sex    Age    Height    Weight

Alfred      M      14     69.0      112.5
Alice       F      13     56.5       84.0
Barbara     F      13     65.3       98.0
Carol       F      14     62.8      102.5
Henry       M      14     63.5      102.5


WORK.CLASS (variables sorted alphabetically)

Age    Height     Name      Sex    Weight
 14     69.0     Alfred      M      112.5
 13     56.5     Alice       F       84.0
 13     65.3     Barbara     F       98.0
 14     62.8     Carol       F      102.5
 14     63.5     Henry       M      102.5