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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

@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

15 REPLIES 15
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

EoghanRussell
Calcite | Level 5

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

Reeza
Super User

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. 

 

 

EoghanRussell
Calcite | Level 5

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.

Reeza
Super User

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.

PaigeMiller
Diamond | Level 26

Why %upcase and not just plain old upcase?

--
Paige Miller
Reeza
Super User

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

ChrisNZ
Tourmaline | Level 20

@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

EoghanRussell
Calcite | Level 5

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 

EoghanRussell
Calcite | Level 5

Thanks for all the help guys. Much appreciated.

Reeza
Super User

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

PaigeMiller
Diamond | Level 26

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
EoghanRussell
Calcite | Level 5

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.

ChrisNZ
Tourmaline | Level 20

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

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
  • 15 replies
  • 1080 views
  • 3 likes
  • 5 in conversation