BookmarkSubscribeRSS Feed
Saurabh_Rana
Obsidian | Level 7

In a SAS table, there are more than 100 columns with variable name length of more than 32 characters, instead of renaming all the variables manually, I want to write the code that will replace or remove specific character strings from all the columns.

 

For example in the below-mentioned variable, I want to remove character strings "LAST_" and "MTH_" from the variable name.

 

 

Existing Variable Name New Variable Name
X_USED_CAR_LOAN_DPD_LAST_12_MTH_CNT_SUB X_USED_CAR_LOAN_DPD_12_CNT_SUB
X_USED_CAR_LOAN_DPD_LAST_12_MTH_CNT_DBT X_USED_CAR_LOAN_DPD_12_CNT_DBT
X_USED_CAR_LOAN_DPD_LAST_12_MTH_CNT_LSS X_USED_CAR_LOAN_DPD_12_CNT_LSS
X_USED_CAR_LOAN_DPD_LAST_12_MTH_CNT_XXX X_USED_CAR_LOAN_DPD_12_CNT_XXX

Note:- This is not an actual table, I just took four variable names for explanation purpose

 

 

If I rename the variable names will the label name change as well, currently variable names and label names are the same?

7 REPLIES 7
PaigeMiller
Diamond | Level 26

Are you talking about doing something to the actual variable names in a SAS data set, or doing something to the text strings in the table you showed us? These are two very different tasks, its not clear which you mean.

--
Paige Miller
Saurabh_Rana
Obsidian | Level 7
I want to actually replace the SAS table variable name, text string was just an example of how the code is supposed to work. Sorry for the confusion, will rephrase the question itself.
PaigeMiller
Diamond | Level 26

UNTESTED CODE

 

data temporary;
    set sashelp.vcolumn(where=(libname='yourlibraryname' and memname='yourdatasetname'));
    newname=tranwrd(name,'last_','');
    newname=tranwrd(newname,'mth_','');
run;
proc sql noprint;
    select distinct cats(name,'=',compress(newname)) into :renames separated by ' '
    from temporary;
quit;
proc datasets library=yourlibraryname nolist;
     modify yourdatasetname;
     rename &renames;
run;
quit;

where you will have to replace yourlibraryname with the actual name of your SAS library, and you will have to replace yourdatasetname with the actual name of your data set.

--
Paige Miller
andreas_lds
Jade | Level 19

If there is a variable named X_USED_CAR_LOAN_DPD_LAST_11_MTH_CNT_DBT in your dataset, then transposing it from wide to long will solve the trouble with fancy long names automatically.

Saurabh_Rana
Obsidian | Level 7
Sorry I did not understand your solution, can you please elaborate.
andreas_lds
Jade | Level 19

@Saurabh_Rana wrote:
Sorry I did not understand your solution, can you please elaborate.

If you have variables named X_USED_CAR_LOAN_DPD_LAST_11_MTH_CNT_SUB and X_USED_CAR_LOAN_DPD_LAST_12_MTH_CNT_SUB then you should think about transposing the data to move information, in this case the month, into a variable, allowing easier usage of the data in most subsequent steps.

Ksharp
Super User
data have;
input old $40.;
cards;
X_USED_CAR_LOAN_DPD_LAST_12_MTH_CNT_SUB	
X_USED_CAR_LOAN_DPD_LAST_12_MTH_CNT_DBT	
X_USED_CAR_LOAN_DPD_LAST_12_MTH_CNT_LSS	
X_USED_CAR_LOAN_DPD_LAST_12_MTH_CNT_XXX
;
data want;
 set have;
 new=prxchange('s/last_|mth_//i',-1,old);
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 7 replies
  • 3935 views
  • 0 likes
  • 4 in conversation