Renaming of variable

[sss]

Hi All,

My code create hundred of sas datasets, and this datasets are imported to excel. Depending up on business requirement required datasets are exported to excel file.below datasets has variable end with crs_i_fmt, I want to rename this variables and should not ends with "crs_i_fmt". some dataset many not variables which ends with crs_i_fmt in such case it should rename variables.

sample input and output are shown below.

table name	has variables	new variables
dataset1	d_time_with_bank_crs_i_fmt	d_time_with_bank
dataset2	X_DAY_SA12_crs_i_fmt	X_DAY_SA12
dataset3	der_time_cross_x4_crs_i_fmt	der_time_cross_x4
dataset4	r_bal_crlim_v1_crs_i_fmt	r_bal_crlim_v1
dataset5	r_tot_dep_exp_v1_crs_i_fmt	r_tot_dep_exp_v1
dataset6	d_resid_status_crs_i_fmt	d_resid_status
dataset7	income_by_dependents_crs_i_fmt	income_by_dependents
dataset8	X_MTH_SA3_BANK_EX14D_crs_i_fmt	X_MTH_SA3_BANK_EX14D
dataset9	c_mnths_cur_empl_crs_i_fmt	c_mnths_cur_empl
dataset10	r_ast_liab_crs_i_fmt	r_ast_liab

thank you for you help

[Steelers_In_DC]

The first thing that comes to mind is going into the code and replacing (ctrl-h) '_crs_i_fmt ' with nothing.  With a space after fmt.  You can count the replacement with the number of datasets to confirm there isn't any fallout.  If you know that will not work You can use proc sql; dictionary columns table to make the change but with 100's of tables a similar type of fallout is possible.

[sss]

Hi mark,

Code is to complex to modify it, it might effect other reports if i modify the code. Renaming is required for only few datasets as a standard process we will not rename the variables.i am trying to write code not getting desired output. it would be great if u share sample code.

[sss · Accepted solution]

below code will do my job. thank you all.

data one;

input d_time_with_bank_crs_i_fmt $50.;

datalines;

1-10

;

run;

data two;

input X_DAY_SA12_crs_i_fmt $50.;

datalines;

1-11

;

run;

data three;

input der_time_cross_x4_crs_i_fmt $50.;

datalines;

1-12

;

run;

%macro dataset_exists(lib,suffix);

    proc contents

            data=&lib.._all_

            out=work.datasets(keep=libname memname name

  where=(upcase(name) contains '&suffix')) noprint;

      run;

      Data null_;                                                

            Set datasets end=end;   

            by memname; 

            if first.memname then do;    

                                                      count+1;

                                                      output;

                                            end;  

            call symputx('dset'||left(count),memname);

            call symputx('old_var'||left(count),name);

             call symputx('new_var'||left(count),tranwrd(name,"_CRS_I_FMT",''));

            if end then call symput('max',count);  

      Run;

      %do i = 1 %to &max;

/*            TITLE Sample records for dataset – &lib..&&dset&i.;*/

            proc datasets lib=&lib;

  modify &&dset&i.;

  rename &&old_var&i. = &&new_var&i.;

            run;quit;

      %end;

%mend;

%dataset_exists(work,_CRS_I_FMT);

%put _user_;