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;
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….

1 comment:

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

    ReplyDelete

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