@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.
In the future please type SAS code into the box opened by clicking on the {i} icon.
