topic Re: Rename macro? in SAS Procedures

Rename macro?

ybz12003 — Thu, 13 Jul 2017 12:31:08 GMT

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

;

Re: Rename macro?

Kurt_Bremser — Thu, 13 Jul 2017 12:44:33 GMT

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;

Re: Rename macro?

art297 — Thu, 13 Jul 2017 12:58:53 GMT

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

Re: Rename macro?

ybz12003 — Thu, 13 Jul 2017 13:57:07 GMT

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.

Re: Rename macro?

Kurt_Bremser — Thu, 13 Jul 2017 14:03:44 GMT

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.

Re: Rename macro?

ybz12003 — Thu, 13 Jul 2017 15:45:00 GMT

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) !! ' ');

Re: Rename macro?

Reeza — Thu, 13 Jul 2017 15:46:52 GMT

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) !! ' ');

Re: Rename macro?

ybz12003 — Fri, 14 Jul 2017 19:46:21 GMT

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.

Re: Rename macro?

Kurt_Bremser — Fri, 14 Jul 2017 21:02:23 GMT

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.

Re: Rename macro?

Tom — Sat, 15 Jul 2017 14:11:29 GMT

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;

Re: Rename macro?

ybz12003 — Sat, 15 Jul 2017 19:35:43 GMT

Hi, Tom:

Yes. I would like to add three underscore between the Pre and Post, which means Name = &Pre___&Post.

Re: Rename macro?

Tom — Sat, 15 Jul 2017 20:12:35 GMT

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

Re: Rename macro?

ybz12003 — Sat, 15 Jul 2017 20:49:13 GMT

Hmm, I only have one 'Pre' and one 'Post' string correspose to one 'NewName', So far!

Re: Rename macro?

ybz12003 — Sat, 15 Jul 2017 20:50:37 GMT

What is this 'where upcase(name) like %upcase("&pre")||'%'||%upcase("&post")' for? Why have % between those two %upcase?

Re: Rename macro?

ybz12003 — Tue, 18 Jul 2017 21:23:13 GMT

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!