Changing the Column positions in SAS

[Anilsk]

How to Change the Column position in SAS?

Suppose 1st column is the Name field then how do i make it the last column?

[ieva]

Column position of variables is based on the order SAS first sees them, you can search and read more about variable order in SAS.

One option (probably not the best, but it works) is to read your dataset in and use format statement before data set mane. Here you can put all variable names in order you want to see them in final dataset (but be careful to use right formats for them not to lose anything).

data yourdata;
input one $ two $ three $;
datalines ;
a b c
;
run;

data yourdata;
format two three one;
set yourdata;
run;

Maybe somebody else can suggest better solution.

[Florent · Accepted solution]

Editor's Note:  Thanks to @Florent for providing an answer using PROC SQL.  Technical Support also has a Usage Note that shows how to do this using a DATA step: Usage Note 8395.

Hello,

You can change the column positions by using a PROC SQL statement.

e.g:

Proc sql;
create table as
select colomn1,
colomn2,
...,
Name
from ;
quit;


I hope it helps.

Regards,
Florent

[sbb]

Why is it necessary to change the SAS internal column location for your variable? With a DATA step approach, a LENGTH, RETAIN or ATTRIB statement, when coded before a SET statement (if one is used), will order SAS variables.

Scott Barry
SBBWorks, Inc.

[deleted_user]

One way to do is to use attrib in data step to define table layout.

[Anilsk]

Thanks, I found the proc sql step much easier and i was able to change the column position easily.

Will read on the attrib options also for the table layout.

Thanks again..

[Peter_C]

Anilsk
the easiest way to change the column order, is to create it in the correct order first, then you won't have to change the order afterwards. Advice you have received on setting column order, should be applied when you create the dataset/table.

There are ways to provide alternative column orders for the same table - a SAS file type called a VIEW.
but you didn't ask that question, so ... I still recommend just fixing the problem before it occurs.

peterC

[sbb]

I still question the need to influence the SAS-internal column, given that the VAR statement and other techniques can be used to control the output side. And, consider that a future SAS programmer who must support your program may not agree that a tidy column order is so important.

So, again -- why is it important to influence the column order, internally within the SAS member? It has no bearing on performance, compression, or otherwise -- only when using, for example, PROC PRINT without a VAR statement control.

Scott Barry
SBBWorks, Inc.

[DavidJ]

I had some SAS code that was creating html tables of SAS datasets and we wanted the output columns to appear in a certain order. The code was not smart enough to parse PROC CONTENTS output, so we issued a PROC SQL to the SAS dataset in the column order we desired.

I could also see wanting the columns in a certain order if using the %DS2CSV macro to create a delimited flat file, which outputs the columns in the order SAS finds them.

I agree with Scott for 99.9% of the cases though.

[Peter_C]

another reason for changing stored column order (but I deprecate 😞 ), it enables a user to review or print data in a different order from the stored order default.
In SAS Enterprise Guide the displayed order seems always to be the stored order, and in old-SAS (Display Manager in a "fat client"), although ViewTable allows us to rearrange the column order displayed, it provides no facility to save/reuse the alternate display order.
In all "clients" I think we (data users and data managers) should more easily be able to separate our data's definition of "display" (a personal or activity based choice) from the definition of its "storage" (as created - so probably defined by, the provider).
in the "fat client" I happily 🙂 use fsview through the SAS explorer, with the most recently used "column display" applied by default, and alternative displays selected via function keys or toolbar icons (or even commands) - from display formulas stored in catalog entries - without having to recreate or reload data.
It seems a hard challenge for SAS Enterprise Guide to beat. - so far ... 😉

[PravinMishra]

Use Proc Sql to define the position.