Re-order variables in SAS

[Datura]

In our daily work, sometimes we need to re-order the variables in a SAS data set.

There are many ways of changing the ordering of variables in SAS. The basic rule is the “Rule of First Encounter: ” the first time that SAS encounters a variable in the course of the construction of a new data set determines the order in which that variable is entered into the new data set.

So, to change the order of variables, you must change the order in which SAS encounters variables. Define a dataset:

 DATA OLD;
 A=1;  B=2;   C=3; 
 RUN;

In this dataset, variables are in order A B C.

DATA NEW;
SET OLD;
LENGTH B A C 3;
RUN;

Variables are still in order A B C, since they are first encountered in Dataset OLD, and that sets the ordering for dataset NEW.

To change the order, ensure that the new order is encountered first:

DATA RENEW;
LENGTH B A C 3;
SET OLD;
RUN;

Now Dataset RENEW has a new ordering.

As mentioned earlier any Statement that lists the variables in the desired order before any other Statement will reorder the variables in the newly created Dataset. The most common are the Retain, Length, Attrib, Label, and Format Statements.

So which one is considered the safest to use ???

Well that distinction falls to the Retain Statement. The reason for this is all variables coming from a input Dataset are automatically Retained. As such using a Retain Statement to reorder variables in a Dataset has no unintended side effect. All other Statements require the programmer to specify some attribute of each variable. DATA RENEW;

 RETAIN B A C;
 SET OLD;
 RUN;

In addition, we can also use Proc SQL to change the ordering of variables.Proc Sql;

create table T2 as
Select C,A,B from T1
where age> 30;
quit;

The problem is that you need to give all the variable names. It is not convenient if you have hundreds of variables to work on.