I'm creating a macro that recursively imports multiple excel files that have multiple sheets within each excel file. Some sheet names contain special characters (i.e. ampersand, % sign) so I want to incorporate %superQ() in my importing macro to mask these special characters. But when I run I get an error: invalid symbolic variable name 'value'. Can someone please let me know what I'm doing wrong? 😕 Below is an extract of my macro : %macro imp;
data _null_;
set file_directory (where=(prxmatch('/(xls|csv)/i',FileType)));
call symput('path'||left(_n_),strip(path));
call symput('filen'||left(_n_),strip(filename));
call symput('table'||left(_n_),cats("File",(_n_)));
call symput('type'||left(_n_),strip(upcase(filetype)));
call symput('stop',strip(_n_));
run;
%do i=1 %to &stop.; /* Enter into excel library to extract sheetnames from each file. */
%if %substr(&&type&i,1,3) eq XLS %then %do;
libname excellib excel "&&path&i.\&&filen&i";
proc sql noprint;
create table sheetname&i. as /* Return a distinct list of sheetnames from each file. */
select distinct Path, Filename, SheetName
from (select "&&path&i." as Path, "&&filen&i" as FileName,
case when prxmatch('/(_xlnm#)/i',memname)
then tranwrd(compress(substr(memname,1,index(memname,'_xlnm#')-1),"$",""),"''","'")
else tranwrd(compress(memname,"$",""),"''", "'") end as SheetName
from sashelp.vstabvw where libname="EXCELLIB");
/* Assign macros to sheet names that are to be imported. */
select count(DISTINCT sheetname) into :cnt_sht
from sheetname&i.;
select DISTINCT sheetname into :sheet1 - :sheet%left(&cnt_sht)
from sheetname&i.;
quit; libname excellib clear;
do j=1 %to &cnt_sht; /* Loop through to import multiple sheets per excel file. */
proc import datafile="&&path&i.\&&filen&i"
out=&&table&i.._SH&j. replace; /* Each output is named with a suffix _SH1 to n (=maximum number of sheets found). */
sheet="%superq(&&sheet&j)"; /*this is where i get an error*/
getnames=yes;
mixed=yes;
scantext=yes;
run;
%end; %mend;
... View more