DATA Step, Macro, Functions and more

Renaming of variable

Accepted Solution Solved
Reply
Contributor sss
Contributor
Posts: 65
Accepted Solution

Renaming of variable

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 namehas variablesnew variables
dataset1d_time_with_bank_crs_i_fmtd_time_with_bank
dataset2X_DAY_SA12_crs_i_fmtX_DAY_SA12
dataset3der_time_cross_x4_crs_i_fmtder_time_cross_x4
dataset4r_bal_crlim_v1_crs_i_fmtr_bal_crlim_v1
dataset5r_tot_dep_exp_v1_crs_i_fmtr_tot_dep_exp_v1
dataset6d_resid_status_crs_i_fmtd_resid_status
dataset7income_by_dependents_crs_i_fmtincome_by_dependents
dataset8X_MTH_SA3_BANK_EX14D_crs_i_fmtX_MTH_SA3_BANK_EX14D
dataset9c_mnths_cur_empl_crs_i_fmtc_mnths_cur_empl
dataset10r_ast_liab_crs_i_fmtr_ast_liab

thank you for you help


Accepted Solutions
Solution
‎08-20-2015 01:54 PM
Contributor sss
Contributor
Posts: 65

Re: Renaming of variable

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_;

View solution in original post


All Replies
Valued Guide
Posts: 858

Re: Renaming of variable

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.

Contributor sss
Contributor
Posts: 65

Re: Renaming of variable

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.

Solution
‎08-20-2015 01:54 PM
Contributor sss
Contributor
Posts: 65

Re: Renaming of variable

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_;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 282 views
  • 0 likes
  • 2 in conversation