SAS macro to reorder dataset variables in alphabetical order

How do you reorder variables in a dataset…I get this many a times….

Here’s a macro for you to achieve it…For example I’ve used a dataset sashelp.flags and created some more variables with variety of variables with names upper / lower cases and _’s to demonstrate the reorder macro….

Please try this macro for yourself and let me know your suggestions….

/* Example dataset with variety of variable names */

data flags;

set sashelp.flags;

a=2;

b=4;

_common=10;

Cool=30;

SubJecT=40;

run

;

%macro reorder(dsn);

/* Get the variables names to a dataset using proc contents and keeping the variable name only */

proc contents data=&dsn

out=varnames(keep=name) noprint;

run;

/* It is very much important that you UPCASE or LOWCASE the variable names...

otherwise you get a different order...Remove this datastep and see for yourself... */

data varnames;

set varnames;

name=lowcase(name);

run;

/* Sort the variable names in alphabetical order */

proc sort data=varnames;

by name;

run;

/* Get the observation count */

data _null_;

set varnames nobs=num;

call symputx('obscnt',num);/* Get the observation count */

call symputx(compress('macvar'||_n_),name); /* Get the variable names into macro variables */

run;

/*Please NOTE that the step of getting all variable names into a macro variable could be simply done by using SQL instead of a macro

proc sql noprint;

select trim(left(name)) into:macvar separated by ' '

from varnames;

quit;

and the next datastep simply

data &dsn;

retain &macvar;

set &dsn;

run;

But the cons here is that if there are too many variables and the total length of all the names put together crosses 32767 bytes the SQL approach would'nt work...

*/

data &dsn;

retain %do i=1 %to &obscnt;

&&macvar&i /* NOTE: there should be a blank space after &&macvar&i to separate the variable names by space eg. retain subject a b c;

NOTE: NO semicolon should be typed here*/

%end;

set &dsn;

run;

%mend reorder;

%reorder(flags);

Interesting References….

Reordering Variables in a SAS® Data Set