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_;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.