how to rearrange the column position in sql proceure

[Mike_Davis]

Hi all,

I have a problem when I try to create a new dataset,change state and city as first and second columns and then keep column from statename to dst,but there is problem in the code:

please help.

Thanks!

proc sql noprint;

create table one as

select a.state

,a.city

,a.statename--a.dst

from sashelp.zipcode as a

;

quit;

[Reeza]

There isn't an easy way to short cut variable selections for SQL procedures.

What I typically do, if I absolutely require the order is run the proc sql with a select * and the feedback option. This writes the full query to the log and then I modify that as required.

Saves typing but the code isn't as succinct as possible.

12   proc sql noprint feedback;

13

14   create table one as

15

16   select *

17

18   from sashelp.zipcode as a

19

20   ;

NOTE: Statement transforms to:

        select A.ZIP, A.Y, A.X, A.ZIP_CLASS, A.CITY, A.STATE, A.STATECODE, A.STATENAME, A.COUNTY, A.COUNTYNM,

A.MSA, A.AREACODE, A.AREACODES, A.TIMEZONE, A.GMTOFFSET, A.DST, A.PONAME, A.ALIAS_CITY

          from SASHELP.ZIPCODE A;

NOTE: Table WORK.ONE created, with 41761 rows and 18 columns.

21

22   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.14 seconds

      cpu time            0.09 seconds

[Tom]

Here is a useful trick for getting variable lists expanded.  Use PROC TRANSPOSE to make a dataset with the variable names.

You can then pull the list into a macro variable.

proc transpose data=sashelp.zipcode(obs=0) out=_names_;

  var state city statename--dst ;

run;

proc sql noprint ;

select _name_ into :varlist separated by ', ' from _names_;

create table one as select &varlist from sashelp.zipcode ;

quit;

[Howles]

Or, avoid the PROC TRANSPOSE step by letting the ORDER BY clause and the KEEP= option do the work.

select name
 into :varlist separated by ', ' 
 from dictionary.columns
 where libname EQ 'SASHELP' and memname EQ 'ZIPCODE'
 order by name NE 'STATE', name NE 'CITY', varnum ;

create table one(keep = state city statename--dst) as
 select &varlist
  from sashelp.zipcode ;

Tom wrote:

Here is a useful trick for getting variable lists expanded.  Use PROC TRANSPOSE to make a dataset with the variable names.
You can then pull the list into a macro variable.

proc transpose data=sashelp.zipcode(obs=0) out=_names_;
  var state city statename--dst ;
run;
proc sql noprint ;
select _name_ into :varlist separated by ', ' from _names_;
create table one as select &varlist from sashelp.zipcode ;
quit;

[MikeZdeb]

hi ... the '--' works as a data set option, so (no NOPRINT needed when you create a table, no table alias A needed either) ...

proc sql;

create table one as

select *

from sashelp.zipcode (keep=state city statename--dst);

quit;

[Tom]

But the keep option will not change the order of the variables.  So in this case CITY ends up before STATE.

[Mike_Davis]

Could this be solved by using proc iml?

[PGStats]

If you don't mind the warnings, you can use:

proc sql;

create table one as

select state, city, *

from sashelp.zipcode(keep=state city statename--dst);

quit;

not very clean, but quick.

PG

[MikeZdeb]

hi ... since no one else has asked, do you have to use SQL ...

data one;

retain state;

set sashelp.zipcode (keep=state city statename--dst);

run;

[Mike_Davis]

Thank you Mike,

This is a good method while I prefer it will be in sql or IML procedure,

retain statement still need us list each variable which we want to rearrange.

[Linlin]

Your statement "retain statement still need us list each variable" is not true. You only need to list "state" and "city" in retain statement.

[MikeZdeb]

Hi actually, you only have to list STATE (I edited my posting !!!).

[Mike_Davis]

Sorry for I didn't express my opinon clearly,I have revised it:

retain statement still need us list each variable which we want to rearrange.

[data_null__]

Mike.Davis wrote:

Sorry for I didn't express my opinon clearly,I have revised it:
retain statement still need us list each variable which we want to rearrange.

How can you rearrange the variables without referring to them?

[Mike_Davis]

like use:

proc sql;

select 5th_variable--20th_variable, 2nd_variable

from..

;

...