Nov 8, 2009

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;
%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;
/* 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;
/* Sort the variable names in alphabetical order */
proc sort data=varnames;
by name;
/* 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 */
/*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;
and the next datastep simply
data &dsn;
retain &macvar;
set &dsn;
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*/
set &dsn;
%mend reorder;
Interesting References….

1 comment:

  1. This comment has been removed by a blog administrator.


Note: Only a member of this blog may post a comment.