Reordering variables

[bnarang]

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

[Linlin]

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

[data_null__]

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;

[art297]

: 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.).

[Linlin]

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;

[art297]

: Works for me!  Nice addition!

[bnarang]

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.

[Linlin]

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;

[art297]

agreed!

[data_null__]

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. 

[RichardinOz]

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.

[art297 · Accepted solution]

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;

[AncaTilea]

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!

[DanielSantos]

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;

More on the ATTRIB statement here:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000179227.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt