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

[deleted_user]

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?

[FredrikE]

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

data out;
keep ID B C A SUM;
set in;
run;

[GertNissen]

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.

[DanielSantos]

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

[sbb]

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.

[deleted_user]

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.

[Peter_C]

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

[DanielSantos]

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

[FredrikE]

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

data out;
retain ID B C A SUM;
set in;
run;

[sbb]

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.

[data_null__]

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?

[sbb]

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.

[Peter_C]

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