07-06-2016 09:29 PM
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;
07-06-2016 10:23 PM
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.
07-07-2016 03:21 AM
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