Date: Wed, 17 Jul 1996 10:48:38 EDT
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: Ian Whitlock <whitloi1@WESTATPO.WESTAT.COM>
Subject: Re: Q: How to rearrange variables?
Subject: Q: How to rearrange variables?
Summary: Macro to get alpha-numeric order (slightly improved from
previous SAS-L post).
Respondent: Ian Whitlock <firstname.lastname@example.org>
Dmitry Mesyanzhinov <email@example.com> writes:
>When I run proc means or contents on my dataset,
>variables appear in the order in which they were created.
>There are about 300 variables in the dataset,
>and it would be more convinient if the listing is
>done in, say, alphabetical order. I tried to specify
>the desired sequence of variables in the KEEP option,
>but that didn't work.
>Is there a way to rearrange the variables in a dataset?
You can reorder variables with a RETAIN statement (no value at the
end) at the top of the DATA step. On the other hand if you want to get
alpha-numeric order in a PROC it is simple in 6.11 (6.09e) to make a
macro variable giving the order and then then use it in a VAR statement
in the PROC. Which is best? It depends on how many times you want
alphanumeric order, how large the data set is, and how easily the
variables get out of order. With a large enough data set is may take
many PROC calls to make it worth passing the data just to change the
order of variables. A macro ALPHANUM makes it simple to capture the
desired order. Here is some test code.
/* test data not vars not in alpha order */
data w ; retain y10 y2 x z a c b 1 ; run ;
/* initialization only needed when macvar ^= alpha */
%let alpha = ?; /* I did it anyway */
%alphanum ( data = w ) /* get alphnumeric order */
proc means data = w ;
var &alpha ; /* use the alphanumeric order */
/* to rearrange the data */
data w ;
retain &alpha ;
set w ;
Here is the macro.
%macro alphanum ( data = _LAST_ , macvar = ALPHA , type = ) ;
%* obtain list of variables in alphanumeric order in macro ;
%* variable named by parameter MACVAR ;
%local lib mem ;
%let data = %upcase ( &data ) ;
%if &data = _LAST_ %then %let data = &syslast ;
%if %index ( &data , . ) = 0 %then %let data = WORK.&data ;
%let lib = %qscan ( &data , 1 , . ) ;
%let mem = %qscan ( &data , 2 , . ) ;
%if %upcase ( &macvar ) = ALPHA %then %global alpha ;
proc sql ;
reset noprint ;
create view __v as
left(reverse(name)) as q ,
verify ( calculated q , ' 0123456789' ) as x ,
left(reverse(substr(calculated q,calculated x))) as n1 ,
when calculated x > 1 then
input(left(reverse(substr(calculated q,1,calculated x - 1))),8.)
end as n2
where libname = "&lib" and memname = "&mem"
%if &type = 1 %then and type = "num" ;
%if &type = 2 %then and type = "char" ;
order by n1 , n2 /* n1=character part, n2=numeric part of name */
select name into :&macvar separated by ' ' from __v ;
%mend alphanum ;
If one doesn't have access to 6.11 or 6.09e then drop the second SELECT
statement from the PROC SQL and add DATA step code to create an array
of macro variables giving the data variable names.