topic Re: Replace or remove a specific character string from all the variable names in the table in SAS Programming

Replace or remove a specific character string from all the variable names in the table

Saurabh_Rana — Sun, 17 Oct 2021 14:43:08 GMT

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?

Re: Replace or remove a specific character string from all the variable names in the table

PaigeMiller — Sun, 17 Oct 2021 14:31:39 GMT

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.

Re: Replace or remove a specific character string from all the variable names in the table

Saurabh_Rana — Sun, 17 Oct 2021 14:37:47 GMT

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.

Re: Replace or remove a specific character string from all the variable names in the table

PaigeMiller — Sun, 17 Oct 2021 14:48:04 GMT

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.

Re: Replace or remove a specific character string from all the variable names in the table

andreas_lds — Mon, 18 Oct 2021 07:11:06 GMT

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.

Re: Replace or remove a specific character string from all the variable names in the table

Saurabh_Rana — Mon, 18 Oct 2021 07:19:53 GMT

Sorry I did not understand your solution, can you please elaborate.

Re: Replace or remove a specific character string from all the variable names in the table

andreas_lds — Mon, 18 Oct 2021 07:27:34 GMT

@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.

Re: Replace or remove a specific character string from all the variable names in the table

Ksharp — Mon, 18 Oct 2021 12:41:39 GMT

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;