How do I Reorder a single SAS variable

[Benbo123321]

How do I re-order one SAS variable out of 123 total? I don't wish to use RETAIN if I have to list them all out again. All the questions I have seen related to this talk about how to re-order many variables all at once.

[Reeza]

Do you want to bring that variable to the front? If not then it's the same method. You can use the dictionary tables to help with the listing of variables. My little trick is to use SQL with the FEEDBACK option, grab the code from the log and reorder what I need, if it's only a few.

proc sql feedback;
create table have as
select *
from sashelp.class as A;
quit;

LOG:
147 proc sql feedback;
148 create table have as
149 select *
150 from sashelp.class as A;
NOTE: Statement transforms to:

select A.Name, A.Sex, A.Age, A.Height, A.Weight
from SASHELP.CLASS A;

NOTE: Table WORK.HAVE created, with 19 rows and 5 columns.

151 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.16 seconds
cpu time 0.00 seconds

[hbi]

Hi,

This technique requires the use of RETAIN, but it does not require any typing on your part because macro variable values are generated programatically. 

/* first, create a dataset containing variable names (this example uses SASHELP.CARS) */
PROC SQL;
  CREATE TABLE my_vars AS 
  SELECT libname, memname, name, type, varnum
  FROM DICTIONARY.COLUMNS
  WHERE LIBNAME = 'SASHELP'
    AND MEMNAME = 'CARS';
QUIT;

  

Next, you can change the sort order of the above dataset via an "ORDER BY" statement while creating the macro variable. Version 1 reorders variables by type. Version 2 reorders variables by alpha order. 

/* version 1: create macro variable with variable names sorted by type */
PROC SQL;
  TITLE 'variables ordered by type';
  SELECT name INTO :var_list_by_type SEPARATED BY " "
  FROM my_vars
  ORDER BY type DESC, varnum;
QUIT;
%PUT &var_list_by_type;

DATA var_position_by_datatype;
  RETAIN &var_list_by_type;
  SET SASHELP.CARS;
RUN;


/* version 2: create macro variable with variable names sorted by name */
PROC SQL;
  TITLE 'variables ordered by name';
  SELECT name INTO :var_list_by_name SEPARATED BY " "
  FROM my_vars
  ORDER BY name;
QUIT;
%PUT &var_list_by_name;

DATA var_position_by_name;
  RETAIN &var_list_by_name;
  SET SASHELP.CARS;
RUN;

I think this next version might address your needs. It moves a variable from position 6 to between 2 and 3. I used the number 2.5 to achieve this.

/* version 3: create macro variable with variable names sorted by name */
/* move the field MSRP from 6th position to in between 2 and 3 */
DATA my_vars_moved;
  SET my_vars;
  IF name = 'MSRP' THEN varnum = 2.5;
RUN;

PROC SORT DATA=my_vars_moved;
  BY varnum;
RUN;

/* create macro variable with variable names sorted the way I want */
PROC SQL;
  TITLE 'variables ordered the way I want';
  SELECT name INTO :var_list_want SEPARATED BY " "
  FROM my_vars_moved
  ORDER BY varnum;
QUIT;
%PUT &var_list_want;

DATA var_position_moved;
  RETAIN &var_list_want;
  SET SASHELP.CARS;
RUN;

  

[Bintang18]

I found this page while looking for rearrange multiple vars in a large dataset, and the program by hbi was exactly what I was looking for, especially the macro. Thanks a lot 

BScholtz

[data_null__]

Depends on where you want to move it to. Do you want a general "make it VARNUM=n" solution?