topic Re: Removing a variable pre-fix within a macro in SAS Programming

Removing a variable pre-fix within a macro

EoghanRussell — Thu, 09 Nov 2017 16:34:54 GMT

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

Re: Removing a variable pre-fix within a macro

RW9 — Thu, 09 Nov 2017 16:53:27 GMT

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.

Re: Removing a variable pre-fix within a macro

Reeza — Thu, 09 Nov 2017 17:03:12 GMT

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.

Re: Removing a variable pre-fix within a macro

EoghanRussell — Thu, 09 Nov 2017 17:04:07 GMT

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

Re: Removing a variable pre-fix within a macro

PaigeMiller — Thu, 09 Nov 2017 17:05:35 GMT

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;

Re: Removing a variable pre-fix within a macro

EoghanRussell — Thu, 09 Nov 2017 17:10:17 GMT

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.

Re: Removing a variable pre-fix within a macro

EoghanRussell — Thu, 09 Nov 2017 17:20:02 GMT

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;

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.

Re: Removing a variable pre-fix within a macro

Reeza — Thu, 09 Nov 2017 17:26:21 GMT

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.

Re: Removing a variable pre-fix within a macro

PaigeMiller — Thu, 09 Nov 2017 17:54:55 GMT

Why %upcase and not just plain old upcase?

Re: Removing a variable pre-fix within a macro

ChrisNZ — Thu, 09 Nov 2017 23:22:12 GMT

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;

Re: Removing a variable pre-fix within a macro

Reeza — Thu, 09 Nov 2017 23:35:46 GMT

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

Re: Removing a variable pre-fix within a macro

ChrisNZ — Fri, 10 Nov 2017 01:42:56 GMT

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

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

Re: Removing a variable pre-fix within a macro

EoghanRussell — Fri, 10 Nov 2017 09:17:20 GMT

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

Re: Removing a variable pre-fix within a macro

EoghanRussell — Fri, 10 Nov 2017 09:26:39 GMT

Thanks for all the help guys. Much appreciated.

Re: Removing a variable pre-fix within a macro

Reeza — Fri, 10 Nov 2017 15:44:55 GMT

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

Re: Removing a variable pre-fix within a macro

PaigeMiller — Fri, 10 Nov 2017 15:50:53 GMT

@EoghanRussell wrote:

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.

I wonder if this is a capitalization mismatch in this part of your code:

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

In the future please type SAS code into the box opened by clicking on the {i} icon.