I'm trying to get some excel data into SAS. The workbooks contain an unpredictable number of sheets, and I am using the xlsx engine to try to access them. I'm on 9.4 ts level 1m4 on x64eso8r2 platform. My current data has 10 sheets in the workbook, 8 of them I can accesss without a problem, but 2 are an issue. Here is the code I am running: libname multi clear;
libname multi XLSX 'H:\GCTT\site_tracker\multitab\Japan MDC_Study Tracker_20190820-2.xlsx';
%macro dummy;
proc sql;
select count(distinct memname) into: ntables from sashelp.vtable where libname = 'MULTI';
%let ntables = %sysfunc(compress(&ntables.));
select distinct memname from sashelp.vtable where libname = 'MULTI';
select distinct memname into :table1-:table&ntables. from sashelp.vtable where libname = 'MULTI';
run; quit;
%do i =1 %to &ntables.;
data work.t&i;
set MULTI."&&table&i"n;
run;
%end;
%mend dummy;
%dummy; the proc sql returns 10 members in the libname multi as expected: 8 data sets are created without issue, but for the sheets Defintions_Recruitment Status and E708-C086-108 I get a the following error in the log: MLOGIC(DUMMY): %DO loop index variable I is now 7; loop will iterate again. SYMBOLGEN: Macro variable I resolves to 7 MPRINT(DUMMY): data work.t7; SYMBOLGEN: && resolves to &. SYMBOLGEN: Macro variable I resolves to 7 SYMBOLGEN: Macro variable TABLE7 resolves to E7080-C086-108 MPRINT(DUMMY): set MULTI."E7080-C086-108"n; ERROR: Couldn't find range or sheet in spreadsheet ERROR: File MULTI.'E7080-C086-108'n.DATA does not exist. MPRINT(DUMMY): run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.T7 may be incomplete. When this step was stopped there were 0 observations and 0 variables. WARNING: Data set WORK.T7 was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.05 seconds cpu time 0.04 seconds MLOGIC(DUMMY): %DO loop index variable I is now 8; loop will iterate again. SYMBOLGEN: Macro variable I resolves to 8 MPRINT(DUMMY): data work.t8; SYMBOLGEN: && resolves to &. SYMBOLGEN: Macro variable I resolves to 8 SYMBOLGEN: Macro variable TABLE8 resolves to E7080-C086-308 MPRINT(DUMMY): set MULTI."E7080-C086-308"n; MPRINT(DUMMY): run; NOTE: The import data set has 24 observations and 12 variables. NOTE: There were 24 observations read from the data set MULTI.'E7080-C086-308'n. NOTE: The data set WORK.T8 has 24 observations and 12 variables. NOTE: DATA statement used (Total process time): I am stumped. Any ideas on how to either correct this or a viable work around? I've tried a proc import with the same results, different access methods, etc. Thanks, Elaine
... View more
