Hello:
I have a oldname list below (I use proc contents to get that). I am trying to rename the list containing both Pre and Post strings. The Newname will be added three underscores between Pre and Post string. My actual dataset have 300 similar oldname list, I am looking for if I could set up a macro to do it. Please advice how to approach it. Thanks.
data test;
infile datalines dsd;
input Oldname : $150. Pre : $100. Post : $100. Newname : $150.;
datalines;
gr_major_get_yes, gr_major_get, yes, gr_major_get___yes
gr_img_com_abn_metr_gyr, gr_img_com, abn_metr_gyr, gr_img_com___abn_metr_gyr
if_excu_con_mer, if_excu, con_mer, if_excu___con_mer
roi_num_email_view_yes, roi_num_email_view, yes, roi_num_email_view___yes
exe_name_find_com_br_dis_seq, exe_name_find, com_br_dis_seq, exe_name_find___com_br_dis_seq
;
Similar to @Kurt_Bremser's approach, but using a proc sql created macro variable:
data test; infile datalines dsd; input Oldname : $150. Pre : $100. Post : $100. Newname : $150.; datalines; gr_major_get_yes, gr_major_get, yes, gr_major_get___yes gr_img_com_abn_metr_gyr, gr_img_com, abn_metr_gyr, gr_img_com___abn_metr_gyr if_excu_con_mer, if_excu, con_mer, if_excu___con_mer roi_num_email_view_yes, roi_num_email_view, yes, roi_num_email_view___yes exe_name_find_com_br_dis_seq, exe_name_find, com_br_dis_seq, exe_name_find___com_br_dis_seq ; data have; input gr_major_get_yes gr_img_com_abn_metr_gyr if_excu_con_mer roi_num_email_view_yes exe_name_find_com_br_dis_seq; cards; 1 2 3 4 5 5 4 3 2 1 ; proc sql noprint; select catt(oldname,'=',newname) into :renames separated by ' ' from test ; quit; data want; set have (rename=(&renames.)); run;
Art, CEO, AnalystFinder.com
data _null_;
set test end=done;
if _n_ = 1 then call execute('data want; set have; rename ');
call execute(trim(oldname) !! '=' !! trim(newname) !! ' ');
if done then call execute ('; run;');
run;
So, can I put 'where' or 'if' statement in front of the call execute? Something like, where oldname in ('pre' and 'post') ?
call execute(trim(oldname) !! '=' !! trim(newname) !! ' ');
What happened when you tried it?
@ybz12003 wrote:
So, can I put 'where' or 'if' statement in front of the call execute? Something like, where oldname in ('pre' and 'post') ?
call execute(trim(oldname) !! '=' !! trim(newname) !! ' ');
Hi, Kurt:
I modified your code below. However, I got an error message. Could you help me where went wrong? Thanks.
data test;
infile datalines dsd;
input name : $150. Newname : $150.;
datalines;
gr__major_get__yes, gr_major_get_yes
gr_img_com___abn_metr_gyr, gr_img_com_abn_metr_gyr
if_excu_con_mer__, if_excu_con_mer
_roi_num_email___view_yes_, roi_num_email_view_yes
;
data _null_;
set test end=done;
if _n_ = 1 then call execute('data want; set test; rename ');
call execute(trim(name) !! '=' !! trim(newname) !! ' ');
if done then call execute ('; run;');
run;
NOTE: CALL EXECUTE generated line.
1 + data want; set test; rename
2 + gr__major_get__yes=gr_major_get_yes
3 + gr_img_com___abn_metr_gyr=gr_img_com_abn_metr_gyr
4 + if_excu_con_mer__=if_excu_con_mer
5 + _roi_num_email___view_yes_=roi_num_email_view_yes
6 + ; run;
WARNING: The variable gr__major_get__yes in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable gr_img_com___abn_metr_gyr in the DROP, KEEP, or RENAME list has never been
referenced.
WARNING: The variable if_excu_con_mer__ in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable _roi_num_email___view_yes_ in the DROP, KEEP, or RENAME list has never been
referenced.
NOTE: There were 4 observations read from the data set WORK.TEST.
NOTE: The data set WORK.WANT has 4 observations and 2 variables.
Within the first call execute, you have to use the dataset that contains the variables you want to rename, not the one that contains the lookup for the variable names.
Similar to @Kurt_Bremser's approach, but using a proc sql created macro variable:
data test; infile datalines dsd; input Oldname : $150. Pre : $100. Post : $100. Newname : $150.; datalines; gr_major_get_yes, gr_major_get, yes, gr_major_get___yes gr_img_com_abn_metr_gyr, gr_img_com, abn_metr_gyr, gr_img_com___abn_metr_gyr if_excu_con_mer, if_excu, con_mer, if_excu___con_mer roi_num_email_view_yes, roi_num_email_view, yes, roi_num_email_view___yes exe_name_find_com_br_dis_seq, exe_name_find, com_br_dis_seq, exe_name_find___com_br_dis_seq ; data have; input gr_major_get_yes gr_img_com_abn_metr_gyr if_excu_con_mer roi_num_email_view_yes exe_name_find_com_br_dis_seq; cards; 1 2 3 4 5 5 4 3 2 1 ; proc sql noprint; select catt(oldname,'=',newname) into :renames separated by ' ' from test ; quit; data want; set have (rename=(&renames.)); run;
Art, CEO, AnalystFinder.com
Thanks, all of your prompt replys. Now, I have more chanllenging situation. I got other sources, the names they provided are various. For example, the 'gr_major_get_yes' could become 'gr_major_get__yes' or 'gr_major_get_yes'. However, one things in common is all of them containing 'Pre' string and 'Post' string. Is there a way could modefy your macro codes based on the 'Pre' and 'Post' conditions, instead of 'Oldname'. Thanks.
You can only automate things which have a logic that can be expressed in an algorithm. "might be" is very hard to code 😉
To automate a rename, you have to have a clear rule how the new name is built from the old name.
Or you have a complete lookup list which is as it is.
It sounds like you have a situation where you have names that start with specific lettters and end with other specific letters. How do you want to then transform the name? If is as simple as converting PRExyzPOST into just xyz then you can use simple SAS functions. If it is more complex then perhaps you can write the rules using regular expressions.
Get a list of varaible names, either using PROC CONTENTS or query the DICTIONARY.COLUMNS metadata.
proc contents data=have out=contents noprint; run;
%let pre=PRE;
%let post=POST;
proc sql noprint;
select catx('=',name,substr(name,%length(&pre)+1,length(name)-%length(&pre.&post)))
into :renames separated by ' '
from contents
where upcase(name) like %upcase("&pre")||'%'||%upcase("&post")
;
quit;
data want ;
set have ;
rename &renames;
run;
Hi, Tom:
Yes. I would like to add three underscore between the Pre and Post, which means Name = &Pre___&Post.
@ybz12003 wrote:
Yes. I would like to add three underscore between the Pre and Post, which means Name = &Pre___&Post.
That is even easier, but perhaps you will have multiple names that try to get named to the same thing?
proc sql noprint;
select catx('=',name,"&pre.___&post")
into :renames separated by ' '
from contents
where upcase(name) like %upcase("&pre")||'%'||%upcase("&post")
;
quit;
data _null_;
if &sqlobs > 1 then put 'ERROR: Name conflict.' "&renames" ;
run;
Hmm, I only have one 'Pre' and one 'Post' string correspose to one 'NewName', So far!
What is this 'where upcase(name) like %upcase("&pre")||'%'||%upcase("&post")' for? Why have % between those two %upcase?
Thanks for everyone's great help. I have tried three ways from Kurt, Art and Tom's suggestions. I had difficulty using Call symput, and Proc SQL where statement didn't work either. However, I am appreciated all of experts making effort and spending time to answer my questions. I learned a lot!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.