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

Reply
New Contributor
Posts: 4

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

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;

 

Super User
Posts: 19,869

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

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.

SAS Super FREQ
Posts: 709

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

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

Ask a Question
Discussion stats
  • 2 replies
  • 607 views
  • 0 likes
  • 3 in conversation