General SAS Programming

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

[Bruno_SAS]

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