BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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

;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

 

View solution in original post

14 REPLIES 14
Kurt_Bremser
Super User
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;
ybz12003
Rhodochrosite | Level 12

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

 

 

 

 

Reeza
Super User

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

 

 

 

 


 

ybz12003
Rhodochrosite | Level 12

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.

 

 

Kurt_Bremser
Super User

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.

art297
Opal | Level 21

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

 

 

ybz12003
Rhodochrosite | Level 12

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.

Kurt_Bremser
Super User

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.

Tom
Super User Tom
Super User

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;

 

 

ybz12003
Rhodochrosite | Level 12

Hi, Tom:

 

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

Tom
Super User Tom
Super User

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

 

ybz12003
Rhodochrosite | Level 12

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

ybz12003
Rhodochrosite | Level 12

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

ybz12003
Rhodochrosite | Level 12

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!

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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