BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bnarang
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

13 REPLIES 13
Linlin
Lapis Lazuli | Level 10

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__
Jade | Level 19

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
Opal | Level 21

: 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
Lapis Lazuli | Level 10

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
Opal | Level 21

: Works for me!  Nice addition!

bnarang
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Opal | Level 21

agreed!

data_null__
Jade | Level 19

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
Quartz | Level 8

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
Opal | Level 21

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
Pyrite | Level 9

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
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1580 views
  • 8 likes
  • 7 in conversation