Removing a variable pre-fix within a macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Removing a variable pre-fix within a macro

Hi Guys. I am trying to rename tables of varying size where the common pre-fix is "New_" I would like to be able to do this without having to specify the number of variables but any method I have tried so far has not worked - including using a
proc sql

select into :list etc to then rename.

 

All help appreciated

 

Thanks

 

 

DATA A;

input new_id $4. new_var1 new_var2 new_var3;

datalines;

 

i001 1 2 3

i002 3 4 5

i003 6 7 8

i004 9 10 12

; run;

 

 

%macro replaceprefix(out,end);

 

data temp;

set work.&out.;

 

run;

%LET ds=%SYSFUNC(OPEN(temp,i));

%let ol=%length(NEW_);

%do i=1 %to &end;

%let dsvn&i=%SYSFUNC(VARNAME(&ds,&i));

%let l=%length(&&dsvn&i);

%let vn&i=%SUBSTR(&&dsvn&i,&ol+1,%EVAL(&l-&ol));

%end;

data work.&out.;

 

set temp;

%do i=1 %to &end;

 

&&vn&i=&&dsvn&i;

drop &&dsvn&i;

%end;

%let rc=%SYSFUNC(CLOSE(&ds));

 

run;

%mend replaceprefix;

%replaceprefix(A,4);

 


Accepted Solutions
Solution
‎11-10-2017 04:26 AM
PROC Star
Posts: 2,231

Re: Removing a variable pre-fix within a macro

@Reeza

%upcase would be marginally faster as the sql parser won't execute the function for all values.

You cannot use it for table values though, only for code constants (WORK and &out here).

So

proc sql noprint;
select catx("=", name,tranwrd(upcase(name),'NEW_',''))
into : prefixlist
separated by ' '
from dictionary.columns
where %upcase(libname) = %upcase("WORK") and %upcase(memname) = %upcase("&out") ;
quit;

should be


where upcase(libname) = %upcase("WORK") and upcase(memname) = %upcase("&out") ;

In this example upcase() is just for safety anyway as the dictionary normally returns libnames and memnames (but not var names)  upper cased.

If speed is an issue the dictionary is best avoided and proc contents out= is *much* faster in my experience

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

Re: Removing a variable pre-fix within a macro

Posted in reply to EoghanRussell

Sorry, what are you trying to do, your post is unclear.  If its simply to change variables then a you have many methods available to you, avoid immediately resorting to macro for instance two options, one simple rename of range, the other proc transpose:

data a;
  input new_id $4. new_var1 new_var2 new_var3;
datalines; 
i001 1 2 3
i002 3 4 5
i003 6 7 8
i004 9 10 12
; 
run;
data want;
  set a (rename=(new_var1-new_var3=old_var1-old_var3));
run;

proc transpose data=a out=inter;
  by new_id;
  var new_var:;
run;
proc transpose data=inter out=want2 prefix=old_var;
  by new_id;
  var col1;
run;

The benefit with the first is simplicity, but you need to know the range, but you could get this from a select into.  The transpose is more robust as you can have any number of variables, you don't need to know upfront.  There is also arrays which could achieve the same.

Occasional Contributor
Posts: 6

Re: Removing a variable pre-fix within a macro

Hi RW9. Thank you for the quick response. So the reason I resorted to a macro is I have almost 100 audit tables audit_address, audit_customer etc.
Each of these tables had a different number of columns i.e.

 

audit_address : new_addressline1, new_addressline2.......
audit_customer: new_surname, new_email, new_phone......

 

So I need to change these 100+ tables so that the columns names are 

 

audit_address : addressline1, addressline2.......
audit_customer: surname, email, phone......

 

I hope this is a bit clearer. Some of these tables have over 50 columns so ideally I don't want to have to write these in manually

Super User
Posts: 22,874

Re: Removing a variable pre-fix within a macro

Posted in reply to EoghanRussell

Hey ladies,

 

Your question is unclear.

 

1. Are you renaming tables or variables?

2. Assuming variables, if the prefix is new_ what's it going to be replaced by? 

3. For renaming you shouldn't recreate the variables, use a RENAME statement with PROC DATASETS instead

4. Why is 4 in your macro parameter list? Use either PROC CONTENTS or SASHELP.VCOLUMN to get the column names

5. It really, really, really helps if you both comment and format your code for legibility. 

 

 

Occasional Contributor
Posts: 6

Re: Removing a variable pre-fix within a macro

[ Edited ]

Hi Reeza,

 

Apologies - the code is a combination of snippets I have picked up to try resolve my issue.

 

1. Are you renaming tables or variables?

I am renaming the variables not the tables

 

2. Assuming variables, if the prefix is new_ what's it going to be replaced by? 

I want to remove the prefix - I gave an example in a previous reply

 

3. For renaming you shouldn't recreate the variables, use a RENAME statement with PROC DATASETS instead

I had previously used a rename statement but got a different error.

 

proc sql noprint;
select cats(name,'='tranwrd(name,'NEW_',''))
into : prefixlist

separated by ' '

from dictionary.columns

where libaname = "'WORK" and memname = '&out."  /*out is the data set I am modifiying*/

 

proc datasets library = work nolist;

modify &out.;

rename &prefixlist.;

run;

quit;

 

 

This although working fine outside a macro is giving me an error around  "rename &prefixlist." ERROR 22-322: Expecting a name.

Super User
Posts: 22,874

Re: Removing a variable pre-fix within a macro

Posted in reply to EoghanRussell

1. Make everything the same case. You're usine NEW_ but your data is new_, that's NOT the same thing in a character value. 

2. same with the WHERE condition, make them all upper case

3. Your CATS function is wrong

4. Your quotes are mismatched in the WHERE clause. 

5. I would recommend you start programming in smaller steps so you can see where your errors are. 

 

 

%let out=A;

proc sql noprint;
select catx("=", name,tranwrd(upcase(name),'NEW_',''))
into : prefixlist
separated by ' '
from dictionary.columns
where %upcase(libname) = %upcase("WORK") and %upcase(memname) = %upcase("&out.") ;
quit;

Try this one for starters.

Respected Advisor
Posts: 2,661

Re: Removing a variable pre-fix within a macro

Why %upcase and not just plain old upcase?

--
Paige Miller
Super User
Posts: 22,874

Re: Removing a variable pre-fix within a macro

Posted in reply to PaigeMiller

@PaigeMiller When hitting Dictionary tables the macro %UPCASE works faster I was told once. It does seem to be true from my experience but haven't tested it.

Solution
‎11-10-2017 04:26 AM
PROC Star
Posts: 2,231

Re: Removing a variable pre-fix within a macro

@Reeza

%upcase would be marginally faster as the sql parser won't execute the function for all values.

You cannot use it for table values though, only for code constants (WORK and &out here).

So

proc sql noprint;
select catx("=", name,tranwrd(upcase(name),'NEW_',''))
into : prefixlist
separated by ' '
from dictionary.columns
where %upcase(libname) = %upcase("WORK") and %upcase(memname) = %upcase("&out") ;
quit;

should be


where upcase(libname) = %upcase("WORK") and upcase(memname) = %upcase("&out") ;

In this example upcase() is just for safety anyway as the dictionary normally returns libnames and memnames (but not var names)  upper cased.

If speed is an issue the dictionary is best avoided and proc contents out= is *much* faster in my experience

Occasional Contributor
Posts: 6

Re: Removing a variable pre-fix within a macro

Hi Reeza,

I should probably highlight (and I'm not trying to come across as smart :-) ) that the code works fine once outside the Macro - the only issue I am having in once I place it within a macro.

I have made the changes you suggested except around the cats function - can you explain why catx works better than cats please? Also I think you meant 

catx(name,"=", tranwrd(upcase(name),'NEW_',''))

and not

 

catx("=", name,tranwrd(upcase(name),'NEW_',''))

am I correct?

 

Thanks 

Occasional Contributor
Posts: 6

Re: Removing a variable pre-fix within a macro

Posted in reply to EoghanRussell

Thanks for all the help guys. Much appreciated.

Super User
Posts: 22,874

Re: Removing a variable pre-fix within a macro

Posted in reply to EoghanRussell

EoghanRussell wrote:

Hi Reeza,

I should probably highlight (and I'm not trying to come across as smart :-) ) that the code works fine once outside the Macro - the only issue I am having in once I place it within a macro.

I have made the changes you suggested except around the cats function - can you explain why catx works better than cats please? Also I think you meant 

catx(name,"=", tranwrd(upcase(name),'NEW_',''))

and not

 

catx("=", name,tranwrd(upcase(name),'NEW_',''))

am I correct?

 

Thanks 


No, it works as coded and I had tested it.

Respected Advisor
Posts: 2,661

Re: Removing a variable pre-fix within a macro

[ Edited ]
Posted in reply to EoghanRussell

So let me see if I understand.

 

You have a data set with all variables having a common prefix, such as

new_1 new_2 new_3

 

and you want to rename these to some other common prefix such as 

stv_1 stv_2 stv_3

 

but there depending on the data set, there may be more than 3 variables, or less than 3 variables, or exactly three variables.

 

I would do a PROC CONTENTS and send the result to a SAS data set, let's call this SAS data set _contents_

 

Then (UNTESTED CODE)

 

proc sql;
     select distinct cats(name,'=stv_',substr(name,5)) into
        :rename_macro_var from _contents_ where lowcase(name) eqt 'new_';
quit;
proc datasets library=work;
    modify dataset;
    rename &rename_macro_var;
run;
quit;
--
Paige Miller
Occasional Contributor
Posts: 6

Re: Removing a variable pre-fix within a macro

[ Edited ]
Posted in reply to PaigeMiller

Hi PaigeMiller,

 

Yes you have understood my problem almost exactly. I am trying to remove the prefix altogether. I have tried your method which works fine outside of a macro but once inside I am getting a strange error:

 

%macro bomi_audit(ds_1=, ds_2=, link_21=, link_23=, link_32=, out=);

 

 

proc sql;

connect to odbc(dsn='BOMIN');

create table work.&out. as

 

select * from connection to odbc

(

 

SELECT c.*

 

FROM

[dbbommain].[dbo].[analytics_population] a

left join &ds_1. b

on a.RSI_NO = b.&link_21.

inner join &ds_2. c

on c.&link_32. = b.&link_23.

 

INNER JOIN

(SELECT &link_32., MAX(modified_date) AS MaxDateTime

from [dbbommain].[dbo].[analytics_population] a

left join &ds_1. b

on a.RSI_NO = b.&link_21.

inner join &ds_2. c

on c.&link_32. = b.&link_23.

 

WHERE cast(modified_date as date) <= inv_start_date

GROUP BY &link_32.) grouped_table

ON c.&link_32. = grouped_table.&link_32.

 

AND c.modified_date = grouped_table.MaxDateTime;

)

;

disconnect from odbc;

quit;

 

data work.&out.

 

(keep=NEW: );

set work.&out.;

 

;

run;

proc sql noprint;

select cats(name,'=',tranwrd(name,'NEW_',''))

 

into : prefixlist

separated by ' '

 

from dictionary.columns

where libname = "WORK" and memname = "&out." and name like 'NEW_%';

 

quit;

 

 

proc datasets library = work nolist;

modify &out.;

rename &prefixlist.;

 

run;

quit;

 

%mend bomi_audit;

 

 

 

 

The error message I am getting is:

 

WARNING: Apparent symbolic reference PREFIXLIST not resolved.

NOTE: Line generated by the invoked macro "BOMI_AUDIT".

23 rename &prefixlist.; run;

_

22

76

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

MPRINT(BOMI_AUDIT): rename &prefixlist. run;

ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

PROC Star
Posts: 2,231

Re: Removing a variable pre-fix within a macro

Posted in reply to EoghanRussell

This should work (add function upcase)

 

data OUT    ; 
  NEW_ADDRESSLINE1='B'; NEW_ADDRESSLINE2='A';
run;

proc sql noprint;
  select cats(NAME,'=',tranwrd(NAME,'NEW_',''))
  into :renamelist separated by ' '
  from DICTIONARY.COLUMNS
  where upcase(LIBNAME)="WORK" and upcase(MEMNAME)="OUT" and upcase(NAME) like 'NEW_%';
quit;

proc datasets library = work nolist;
  modify OUT;
  rename &renamelist.;
quit;
☑ This topic is solved.

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

Discussion stats
  • 15 replies
  • 128 views
  • 3 likes
  • 5 in conversation