Help using Base SAS procedures

Rename macro?

Accepted Solution Solved
Reply
Super Contributor
Posts: 297
Accepted Solution

Rename macro?

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

;

 

 


Accepted Solutions
Solution
‎07-18-2017 05:24 PM
PROC Star
Posts: 7,363

Re: Rename macro?

Similar to @KurtBremser'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

 

 

View solution in original post


All Replies
Super User
Posts: 6,946

Re: Rename macro?

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 297

Re: Rename macro?

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

 

 

 

 

Super User
Posts: 17,840

Re: Rename macro?

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

 

 

 

 


 

Super Contributor
Posts: 297

Re: Rename macro?

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.

 

 

Super User
Posts: 6,946

Re: Rename macro?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎07-18-2017 05:24 PM
PROC Star
Posts: 7,363

Re: Rename macro?

Similar to @KurtBremser'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

 

 

Super Contributor
Posts: 297

Re: Rename macro?

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.

Super User
Posts: 6,946

Re: Rename macro?

You can only automate things which have a logic that can be expressed in an algorithm. "might be" is very hard to code Smiley Wink

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,502

Re: Rename macro?

[ Edited ]

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;

 

 

Super Contributor
Posts: 297

Re: Rename macro?

Hi, Tom:

 

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

Super User
Super User
Posts: 6,502

Re: Rename macro?


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;

 

Super Contributor
Posts: 297

Re: Rename macro?

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

Super Contributor
Posts: 297

Re: Rename macro?

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

Super Contributor
Posts: 297

Re: Rename macro?

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!

☑ This topic is SOLVED.

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

Discussion stats
  • 14 replies
  • 440 views
  • 5 likes
  • 5 in conversation