topic Re: Reordering variables in SAS Programming

Reordering variables

bnarang — Sat, 20 Oct 2012 13:22:36 GMT

Hello Everyone

I have a process which runs monthly and creates dataset with variables X1 , X2, X3 and so on. The number of variables changes like X1 X2 X3 X4........ X10 . Before running the program , I don't know how many variables will generate but it generates in series X(num). The problem is in the output Variables are not coming in the right order. They are scrambled like X2 X4 X1 X3. I want to have the result like X1 X2 X3 X4 and so on.

I tried using Retain statment with

Data ... ;

Retain Obs_num X1- X: ;

But this did not work.

Any help would be appreciated.

Thanks

Re: Reordering variables

Linlin — Sat, 20 Oct 2012 13:40:59 GMT

data have;

input x1 x3 x5 x2 x4 obs_num;

cards;

1 3 5 2 4 1

;

%let lib=work;

%let dsn=have;

proc sql noprint;

select name into :names separated by ' '

from sashelp.vcolumn

where libname="%upcase(&lib)" and memname="%upcase(&dsn)"

order by name;

quit;

data want;

retain &names;

set &lib..&dsn;

run;

proc print;run;

Obs obs_num x1 x2 x3 x4 x5

1 1 1 2 3 4 5

Re: Reordering variables

RichardinOz — Sat, 20 Oct 2012 13:50:04 GMT

Here is a quick and dirty (but OK) approach to get you going. You do not need to know how many columns. Transpose 1 row of data from a Nx2 wide to a 2xN table containing _NAME_ (names of original columns) and Col1 the transposed data. Sort by _NAME_; then transpose back again. Use the re-ordered columns as a template for your output data.

Proc Transpose

Data = ProcessData (obs = 1)

Out = Varnames

;

Var _ALL_ ;

Run ;

Proc Sort

Data = Varnames ;

By _NAME_ ;

;

Run ;

Proc Transpose

Data = Varnames

Out = Varcols (Drop = _NAME_) /*Also drop _LABEL_ if your process data has labels*/

;

Run ;

Data ProcessData ;

Length ObsNum 8 ;

Set Varcols (obs = 0)

ProcessData

;

Run ;

If the total number of rows output by your process is small (< ~1000) you can transpose the whole table, sort it, and transpose back.

NB this method assumes you do not have a mix of character and numeric data.

Re: Reordering variables

art297 — Sat, 20 Oct 2012 14:11:13 GMT

You will run into a problem using proc sql unless you first account for the numeric collation issue regarding x10 (i.e., I presume that you don't want x1 x10 x2 x3, etc.).

If you can live with some irrelevant notes the easiest solution is simply:

data want;

retain x1-x10;

set have;

run;

If you can't accept notes, as such, an alternative might be:

proc sql;

create table names as

select name

from dictionary.columns

where libname="WORK" and

memname="HAVE" and

name like "x%"

;

quit;

data names;

set names;

key_num=sortkey(name,,,,'N');

run;

proc sql noprint;

select name into :names

separated by " "

from names

order by key_num

;

quit;

data want;

retain &names.;

set have;

run;

Re: Reordering variables

data_null__ — Sat, 20 Oct 2012 15:44:14 GMT

PROC CONTENTS will produce the correct order for the enumearated variabls.

data have;

input x1 x10 x3 x5 x2 x4 obs_num;

cards;

1 3 5 2 4 1 10

;;;;

run;

proc contents noprint out=vars(keep=name);

run;

proc sql noprint;

select name into :vars separated by ' ' from vars;

quit;

run;

data need;

retain &vars;

set have;

run;

proc print;

run;

Re: Reordering variables

art297 — Sat, 20 Oct 2012 16:12:38 GMT

: I didn't know that! Definitely easier for the OP's purpose. Interestingly, it doesn't work as expected if their are some extra variables in the list (like 9a, 9b, 10a and 10b) where the use of the sortkey function does produce the correct(?) order (e.g., will result with x1, x2, x3, x3a, x3b, x4, etc.).

Re: Reordering variables

Linlin — Sat, 20 Oct 2012 16:32:05 GMT

How about the modified one:

data have;

input x1 x3 x10 x5 x2 x4 x9 x8 x7 x6 obs_num;

cards;

1 3 5 10 2 4 9 8 7 6 1

;

%let lib=work;

%let dsn=have;

proc sql noprint;

select name into :names separated by ' '

from sashelp.vcolumn

where libname="%upcase(&lib)" and memname="%upcase(&dsn)" and name like "x%"

order by put(input(compress(name,,'a'),3.),z3.);

quit;

%put &names;

data want;

retain obs_num &names;

set &lib..&dsn;

run;

proc print;run;

Re: Reordering variables

AncaTilea — Sat, 20 Oct 2012 17:35:55 GMT

Here is a nice, straightforward explanation-by-example of how to re-order variables in SAS.

http://analytics.ncsu.edu/sesug/2002/PS12.pdf

Good luck!

Re: Reordering variables

data_null__ — Sat, 20 Oct 2012 19:11:28 GMT

Yes, what is "correct"? SORKEY is good but how do you get it to work in SQL.

x9a would not be consided a variable in an enumerated list like x1-x10. It depends on what you want I reckon.

Re: Reordering variables

art297 — Sat, 20 Oct 2012 20:03:30 GMT

: Works for me! Nice addition!

Re: Reordering variables

bnarang — Sun, 21 Oct 2012 03:55:58 GMT

Thank you everyone. I have got quite a options to solve the problem. It' s very difficult to choose the correct Answer since all of them are correct.(or we have choice to select multiple correct?)

Thanks a lot.

Re: Reordering variables

Linlin — Mon, 22 Oct 2012 00:21:59 GMT

Thank you for your encouragement! The updated code also orders extra variables in the list (like 9a, 9b, 10a and 10b):

data have;

input x1 x3b x3 x3a x10 x5 x2 x4 x9 x3d x8 x7 x6 obs_num;

cards;

1 32 3 31 10 5 2 4 9 34 8 7 6 1

;

%let lib=work;

%let dsn=have;

proc sql noprint;

select name into :names separated by ' '

from sashelp.vcolumn

where libname="%upcase(&lib)" and memname="%upcase(&dsn)" and name like "x%"

order by put(input(compress(name,,'a'),3.),z3.) ,substr(name,length(name));

quit;

%put &names;

data want;

retain obs_num &names;

set &lib..&dsn;

run;

proc print;run;

Re: Reordering variables

art297 — Mon, 22 Oct 2012 00:28:57 GMT

agreed!

Re: Reordering variables

DanielSantos — Mon, 22 Oct 2012 09:10:15 GMT

Here's another one, and an old trick that I use...

If you don't mind about notes AND the variable labels, use the ATTRIB statement before the SET, to force the reordering of the columns.

data want;

attrib x1-x10 label='';

set have;

run;