Reorder or Rearrange column name in SAS

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

  • Reorder or Rearrange the column name in SAS – SAS Retain statement
  • Reorder the column names in descending order in SAS
  • Reorder 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 – Rearrange column using Retain statement:

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

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

 

Rearrange Column name in SAS using Retain Statement

Rearrange 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 rearranged 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

 

 

Reorder 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 reordering 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

 

 

 

Reorder 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 reordering 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

 

                                                                                             

Author

  • Sridhar Venkatachalam

    With close to 10 years on Experience in data science and machine learning Have extensively worked on programming languages like R, Python (Pandas), SAS, Pyspark.

    View all posts