Re Arrange or Re order column name in SAS

In order to Re order or Re arrange column name in SAS, we will using Retain statement. We can also re order the column name in descending order in SAS in roundabout way.  We can also re order the column name in ascending order in SAS. Let’s see an Example of each

  • Re arrange the columns in SAS – SAS Retain statement
  • Re order the column names in descending order in SAS
  • Re order the column names in ascending order in SAS

We will be using the table name CARS.

Re Arrange or Re order column name in SAS 1

 

Syntax – Re arrange column using Retain statement:

Data want;
Retain  col1 col2 col3;
Set old_table;
Run;

Col1, Col2, Col3 are the exact order of the columns

 

Re Arrange Column name in SAS using Retain Statement

Re Arrange the column name in SAS using retain statement takes the column names in specific order and maintains the same order in resultant table there by column name is re arranged as we mention


/* Rearrange column name in SAS */ 

data cars; 
    retain PRICE GEARS HP CYLINDER LUXURY MAKE MPG; 
	set cars; 
run;

So the resultant table which is re arranged will be

Re Arrange or Re order column name in SAS 2

 

 

Re Order the Column name in Ascending order : SAS

Step 1:  Sort the column names by ascending order

This done in roundabout way using proc contents and proc sort and the column is sorted by its name as shown below. Column name is sorted in ascending order and stored in “sorted_cols”



proc contents data=cars out=col_name(keep=name) noprint; 
run; 
proc sort data=col_name out=col_names_sorted; 
  by name; 
run; 
 
data _null_; 
 set col_names_sorted; 
 by name; 
 retain sorted_cols; 
 length sorted_cols $2500.; 
 if _n_ = 1 then sorted_cols = name; 


 

Step 2 : Retain that ascending order there by re ordering in ascending order


data output_sorted; 
   retain &sorted_cols; 
   set cars; 
run; 

So the resultant column which is sorted in ascending order will be

Re Arrange or Re order column name in SAS 3

 

 

 

Re Order the Column name in Descending order : SAS

Step 1:  Sort the column names by descending order

This done in roundabout way using proc contents and proc sort and the column is sorted by its name as shown below. Column name is sorted in descending order and stored in “sorted_cols”

  set col_names_sorted; 
  by descending name; 
  retain sorted_cols; 
  length sorted_cols $2500.; 
  if _N_ =1 then sorted_cols = name; 
  else sorted_cols = catx(' ', sorted_cols, name); 
  call symput('sorted_cols', sorted_cols); 
run; 
 
 
data output_sorted; 
   retain &sorted_cols; 
   set cars; 
run; 


 

Step 2 : Retain that descending order there by re ordering in descending order


data output_sorted; 
   retain &sorted_cols; 
   set cars; 
run;

So the resultant column which is sorted in descending order will be

Re Arrange or Re order column name in SAS 4