Removing a variable pre-fix within a macro

[PaigeMiller]

---

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