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
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_;
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.
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.
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_;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.