topic Re: I want to change the column order in large dataset in SAS Procedures

I want to change the column order in large dataset

AZ_KI — Tue, 26 May 2015 08:50:53 GMT

I have a table with 16 million rows and I would to add a column to the table which I did with :

proc sql;

alter table A

add green num format 8.

quit;

The problem I have now is that I want the columns to be in a specific order. Let's say that I want the new column to be the first one when you read from left.

Does anyone have a clue about how I can do that without creating a new table or read up the whole dateset again(because it takes about one hour)?

Thanks in advance.

Re: I want to change the column order in large dataset

Ksharp — Tue, 26 May 2015 12:39:38 GMT

How about creating a VIEW not TABLE ,if you have a big table .

Re: I want to change the column order in large dataset

RW9 — Tue, 26 May 2015 13:05:29 GMT

Why do you need them in a certain order? It is irrelevant in programming except in certain situations. If it is reporting then set the order in the report program.

Re: I want to change the column order in large dataset

Kurt_Bremser — Tue, 26 May 2015 13:15:09 GMT

Because the ordering of the columns is part of the physical file layout, you cannot reorder the variables without rewriting the dataset.

Since you also need to rewrite the whole dataset to add a column, you can specify the new ordering at this moment.

a) by specifying the wanted order in the SELECT part of a CREATE TABLE statement in PROC SQL.

b) by specifying the variable ordering in an "empty" FORMAT statement before the SET statement in a DATA step.

Re: I want to change the column order in large dataset

BrunoSilva — Tue, 26 May 2015 15:27:49 GMT

There's several techniques to archive this you can check a few in the links bellow, but i think you need always to re-write the table.

Re-ordering variables - sasCommunity

8395 - How to reorder the variables in a SAS® data set