BookmarkSubscribeRSS Feed
Benbo123321
Calcite | Level 5
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.
4 REPLIES 4
Reeza
Super User
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
Quartz | Level 8 hbi
Quartz | Level 8

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. Robot Happy

 

/* 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;

  dictionary_columns_sashelp_cars.gif

 

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;

  dictionary_columns_sashelp_cars_moved.gif

Bintang18
Obsidian | Level 7

 

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__
Jade | Level 19
Depends on where you want to move it to. Do you want a general "make it VARNUM=n" solution?

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
  • 4 replies
  • 1097 views
  • 1 like
  • 5 in conversation