Return to Snippet

Revision: 22956
at January 26, 2010 14:30 by sastechies

Initial Code
options nosource nomprint nomlogic nosymbolgen;
%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 symput('obscnt',num);/* Get the observation count */ 
	call symput(compress('macvar'||_n_),trim(left(name))); /* Get the variable names into macro variables */ 

%let obscnt=&obscnt; /*remove the leading and trailing blankspaces generated when numeric is converted to Best12. format */ 
%put obscnt=&obscnt;
/*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;

/* Example dataset with variety of variable names */ 
data flags; 
set sashelp.flags; 

ods listing close;
ods html body="C:\Reorder.html";
title 'Order of the Variable Before Re-ordering';
proc contents data=flags; run;


title 'Order of the Variable after Re-ordering';
proc contents data=flags; run;
ods html close;
ods listing;

Initial URL

Initial Description

Initial Title
SAS Macro to reorder dataset variables in alphabetical order...

Initial Tags

Initial Language