BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Anilsk
Fluorite | Level 6
How to Change the Column position in SAS?

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

Accepted Solutions
Florent
Quartz | Level 8

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

View solution in original post

10 REPLIES 10
ieva
Pyrite | Level 9
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
Quartz | Level 8

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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
One way to do is to use attrib in data step to define table layout.
Anilsk
Fluorite | Level 6
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
Rhodochrosite | Level 12
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Calcite | Level 5
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
Rhodochrosite | Level 12
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
Quartz | Level 8

Use Proc Sql to define the position.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 67320 views
  • 3 likes
  • 8 in conversation