issue invoking macro

[Satori]

I'm using the following macro:

option mprint symbolgen;
%macro ex(dsin,dsout);
%local i keynames keep_list rename_list;
%let keynames=Cname Country ISO ID Status Form Type Code Filing
	NACE NAICS SIC;
%do i=2008 %to 2022;
proc contents data=&dsin out=list(keep=name) noprint; run;
proc sql noprint; select nliteral(name), catx('=', nliteral(name), substr(name, 1, length(name)-5)) into
	:keep_list separated by ' ', :rename_list separated by ' ' from list where upcase(trim(name)) like "%_&i"; quit;
data b_&i; set &dsin(keep=&keynames &keep_list); %if (&sqlobs) %then %do; rename &rename_list; %end run;
%end;
data &dsout; set b_2008-b_2022; run;
%mend;

proc import datafile="/data/Export_us.xlsx" out=us dbms=xlsx replace;
%ex(us,o_us);

proc import datafile="/data/Export_dc.xlsx" out=dc dbms=xlsx replace;
%ex(dc,o_dc);

option nomprint nosymbolgen;
*end of macro;

My problem is that both o_us and o_dc (the output files) are the same. Any idea why this is happening?

[Quentin]

Can you show the log from running this code?

If I had to guess, this clause:

where upcase(trim(name)) like "%_&i"

Might be returning 0 obs, which would mean the macro variable RENAMELIST will be empty.

But if you show the log, from running the code with MPRINT and SYMBOLGEN turned on, it should be easier to tell what's happening.

Are you just transposing this data from wide (with lots of variables like x_2001 y_2001 x_2002 y_2002 to long?  If so, you might want to look into use PROC TRANPOSE, or transposing it using a singe DATA step with arrays.

[Tom]

What do you get when you turn on MPRINT?  Not sure that SYMBOLGEN is going to help with debugging in this case, it will just clutter the SAS LOG.

It might help to clean up the formatting (perhaps posting into the forum has removed some of the white space?).

That made it easier to notice that

1) The the PROC CONTENTS should be before the %DO loop.

2) The second %END is missing the ending semicolon.

Are you trying to check for literal _ character in the end of the variable names? If so you need to use and escape character.

Does this help?

%macro ex(dsin,dsout);
%local i keynames keep_list rename_list;
%let keynames=Cname Country ISO ID Status Form Type Code Filing
	NACE NAICS SIC
;
proc contents data=&dsin out=list(keep=name) noprint; run;
%do i=2008 %to 2022;
proc sql noprint;
select nliteral(name)
     , catx('=', nliteral(name), substr(name, 1, length(name)-5))
  into :keep_list separated by ' '
     , :rename_list separated by ' ' 
  from list 
  where upcase(trim(name)) like "%^_&i" escape '^'
; 
quit;
data b_&i;
  set &dsin(keep=&keynames &keep_list);
  %if (&sqlobs) %then %do;
  rename &rename_list; 
  %end;
run;
%end;
data &dsout; 
  set b_2008-b_2022; 
run;
%mend;