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);
%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
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.
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
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.
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.
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.
Why %upcase and not just plain old upcase?
@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.
%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
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
Thanks for all the help guys. Much appreciated.
@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.
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;
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.
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;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.