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

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

deleted_user — Mon, 02 Nov 2009 08:29:51 GMT

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?

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

FredrikE — Mon, 02 Nov 2009 09:08:28 GMT

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

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

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

DanielSantos — Mon, 02 Nov 2009 10:26:19 GMT

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

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

GertNissen — Mon, 02 Nov 2009 12:09:03 GMT

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.

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

DanielSantos — Mon, 02 Nov 2009 12:15:38 GMT

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

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

sbb — Mon, 02 Nov 2009 13:55:54 GMT

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.

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

FredrikE — Mon, 02 Nov 2009 15:06:26 GMT

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

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

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

sbb — Mon, 02 Nov 2009 16:02:25 GMT

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.

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

data_null__ — Mon, 02 Nov 2009 18:58:02 GMT

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?

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

sbb — Mon, 02 Nov 2009 19:54:12 GMT

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.

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

deleted_user — Mon, 02 Nov 2009 20:15:05 GMT

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.

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

Peter_C — Tue, 03 Nov 2009 09:21:37 GMT

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

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

Peter_C — Tue, 03 Nov 2009 09:32:10 GMT

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