Importing excel file with sheet name that has ampersand or percent sign using %superq() in macro sas

[dl0324]

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;

[Reeza]

I dont have an answer for your question but that seems overly complex.

If you can assign a libname can you use proc copy instead. Or can you use NLITERAL() to read in sheet names.

[BrunoMueller]

Hi

Check the doc for the %SUPERQ function, you must specify the name of a macro var without the ampercent.

However you can use an &suffix, see also code below

%let sheet1 = %nrstr(cars&more);
%let sheet2 = %nrstr(others&more);

%let i = 1;

%put NOTE: *%superq(sheet&i)*;

proc import
  datafile="c:\temp\cars.xlsx" 
  out=work.sample 
  dbms=xlsx
  replace
;
  sheet="%superq(sheet&i)";
  getnames=yes;
run;

Bruno

[rajupottala]

data ab;
input name$ @@;
datalines;
a b c d e f g h i j k
run;
/* above-mentioned observations are sheet names
we can link this to our sheet name and dataset name*/

data _null_;
set ab nobs=total;
call symputx("sheet"||left(_n_),name);
call symputx("cnt",total);
run;
%put &sheet2;/* check it's resovling or not in log*/
%put &cnt; /* check it's resovling or not in log*/

%macro import();
%do i=1 %to &cnt;
proc import datafile="G:\SAS Practice\Result\raju.xlsx"
out=&&sheet&i
dbms=xlsx
replace;
sheet="&&sheet&i";
run;
%end;
%mend import;
%import();

[sam01]

thanks <a href="https://softballabox.com/">Softball Drills</a>