topic Re: how to rearrange the column position in sql proceure in SAS Procedures

how to rearrange the column position in sql proceure

Mike_Davis — Tue, 01 May 2012 15:37:40 GMT

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;

Re: how to rearrange the column position in sql proceure

Reeza — Tue, 01 May 2012 16:52:48 GMT

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;

14 create table one as

16 select *

18 from sashelp.zipcode as a

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.

22 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.14 seconds

cpu time 0.09 seconds

Re: how to rearrange the column position in sql proceure

Tom — Tue, 01 May 2012 17:12:51 GMT

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;

Re: how to rearrange the column position in sql proceure

MikeZdeb — Tue, 01 May 2012 17:13:24 GMT

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;

Re: how to rearrange the column position in sql proceure

Tom — Tue, 01 May 2012 17:23:52 GMT

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

Re: how to rearrange the column position in sql proceure

Mike_Davis — Tue, 01 May 2012 17:54:32 GMT

Could this be solved by using proc iml?

Re: how to rearrange the column position in sql proceure

PGStats — Tue, 01 May 2012 17:56:44 GMT

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.

Re: how to rearrange the column position in sql proceure

MikeZdeb — Tue, 01 May 2012 18:28:35 GMT

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;

Re: how to rearrange the column position in sql proceure

Mike_Davis — Tue, 01 May 2012 19:46:44 GMT

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.

Re: how to rearrange the column position in sql proceure

Linlin — Tue, 01 May 2012 20:07:52 GMT

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

Re: how to rearrange the column position in sql proceure

MikeZdeb — Tue, 01 May 2012 20:14:35 GMT

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

Re: how to rearrange the column position in sql proceure

Mike_Davis — Tue, 01 May 2012 20:28:39 GMT

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.

Re: how to rearrange the column position in sql proceure

data_null__ — Tue, 01 May 2012 20:39:43 GMT

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?

Re: how to rearrange the column position in sql proceure

Mike_Davis — Wed, 02 May 2012 00:07:09 GMT

like use:

proc sql;

select 5th_variable--20th_variable, 2nd_variable

from..

;

...

Re: how to rearrange the column position in sql proceure

Howles — Wed, 02 May 2012 01:55:14 GMT

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;

Re: how to rearrange the column position in sql proceure

Ksharp — Wed, 02 May 2012 05:02:52 GMT

So did you try the method proposed by Howels ? I like it.

Querying dictionary can get whatever order you want.

Ksharp