DATA Step, Macro, Functions and more

To change the order of variables(columns) in a SAS-table.

Reply
N/A
Posts: 0

To change the order of variables(columns) in a SAS-table.

My table has variables ID, A, B and C, in that order. I assign the sum of A, B and C to the variable SUM.

The order of the variables in my table then is ID, A, B, C, SUM.

For some reason I want the order to be: ID, B, C, A, SUM.

How can that be coded?
Frequent Contributor
Posts: 120

Re: To change the order of variables(columns) in a SAS-table.

Thera are many ways to do this, but the easiest must be:

data out;
keep ID B C A SUM;
set in;
run;
SAS Employee
Posts: 160

Re: To change the order of variables(columns) in a SAS-table.

I dont understand why this will work, when I try

data test;
keep age name ;
set sashelp.class;
run;

I get name before age?

The only way I can get this to work is either by using proc SQL or by using the attrib (or format/lenght) in the datastep.
Super Contributor
Posts: 474

Re: To change the order of variables(columns) in a SAS-table.

You're right Geniz.

Keep wouldn't do the trick, since it just filters columns names into the dataset without changing the actual position.

Only SQL, ATTRIB or LENGTH statements will reorder the position of columns into a new dataset.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
Super Contributor
Super Contributor
Posts: 3,174

Re: To change the order of variables(columns) in a SAS-table.

Why is it important to influence the SAS-internal variable order anyway? What are you attempting to accomplish by doing so?

Scott Barry
SBBWorks, Inc.
N/A
Posts: 0

Re: To change the order of variables(columns) in a SAS-table.

The table should be exported to Excel.

It should be done directly, in one process, by running the SAS-code, so I am using DDE.

The order of the variables in the original SAS-table is given, and the order in the Excel worksheet is decided to be another.

In the real situation there are many more variables than in my initial example.

Thanks to everyone for your commitment.
Valued Guide
Posts: 2,175

Re: To change the order of variables(columns) in a SAS-table.

ErnestoC

as you write to excel with dde, you can specify the columns in your chosen order,

at that stage (in the data step which writes).

If you chose to write to a new workbook with for example tagsets.excelxp, you could define the required column order in the VAR statement of proc print

Neither method would need the "input data set" to have a specific column order.

Why try harder?

PeterC
Super Contributor
Posts: 474

Re: To change the order of variables(columns) in a SAS-table.

Yep another way would be to use proc sql with the select statement.

proc sql noprint;
create table new_tab from
select ID,B,C,A,SUM from tab;
quit;

Whatever method you will use, it always implies recreating the table with a new structure.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
Frequent Contributor
Posts: 120

Re: To change the order of variables(columns) in a SAS-table.

I was a bit too quick, the right code should be:

data out;
retain ID B C A SUM;
set in;
run;
Super Contributor
Super Contributor
Posts: 3,174

Re: To change the order of variables(columns) in a SAS-table.

As others have observed on this forum, the RETAIN statement should be avoided, unless absolutely necessary. Search the archives for additional comments / discussion on the particular point.

Scott Barry
SBBWorks, Inc.
Respected Advisor
Posts: 3,777

Re: To change the order of variables(columns) in a SAS-table.

There you go again. What is your problem with RETAIN? So, you wrote a program once upon a time that didn't work because you don't understand RETAIN and now you have a vendetta against the poor defenseless RETAIN statement. I’m sorry but the statement “RETAIN should be avoided” sounds ridiculous to me.

What other SAS statements or procedures do you avoid?
Super Contributor
Super Contributor
Posts: 3,174

Re: To change the order of variables(columns) in a SAS-table.

Scan the forum archives - we've been down this path before, but not again.

No question that there are very specific opportunities to use a RETAIN statement, mostly to iterate a DATA step maintaining a "unique named" SAS variable across a step RETURN/DELETE, either explicit or implicit.

If you choose, enjoy using the RETAIN statement to your heart's content.

To do so, unless the need calls for it, is an opportunity for problems with SAS application program maintenance -- obviously my personal opinion.

Scott Barry
SBBWorks, Inc.
Valued Guide
Posts: 2,175

Re: To change the order of variables(columns) in a SAS-table.

Hi Scott

I understand you have some resistance to RETAIN

were you aware that most variables read into a data step will naturally (and unavoidably) RETAIN?

That excludes only new columns derived in a step (created via assignment or input).
(but it does not exclude variables which are named on statement options, like filevar= and in= )

So a proper understanding of RETAIN is possibly (or probably) an "essential" for SAS training. (imho)

Given that proper understanding (and respect), I see no reason to propose avoiding the RETAIN statement as a sensible technique for arranging column order.
(I have probably learned a lot from tripping up over my mistakes, like non-use of RETAIN).
.... just my $0.02

kind regards
PeterC
Ask a Question
Discussion stats
  • 12 replies
  • 277 views
  • 0 likes
  • 7 in conversation