The best way to solve problems like yours: drop excel, use csv files instead (and don't open csv files with excel, but with a text editor).
Use the libname statement to access the excel-file.
Get the names of the sheets from sashelp.vtable.
Combine the sheets using a data step with set, hope that all variable have the same metadata (from my experience this won't be the case in 100% of all excel files i had to process).
Write complex code fixing the issues caused by excel.
Example code:
/* crating an excel-file to demonstrate the steps 1-3 */
proc sort data=sashelp.cars out=work.cars;
by Origin;
run;
option nobyline;
ods excel file="PATH\cars.xlsx" options(sheet_name="#byval1");
proc print data=work.cars noobs;
by Origin;
run;
ods excel close;
/* 1) reading the excel-file */
libname nofun xlsx "PATH\cars.xlsx";
/* 2) Get the names of the sheets in the excel-file */
proc sql noprint;
select cats('nofun.', MemName)
into :SheetList separated by ' '
from sashelp.vtable
where upcase(LibName) = 'NOFUN';
quit;
/* 3) combine the sheets */
data work.combined;
set &sheetList.;
run;
libname nofun clear;
Unfortunately even this didn't work as expected:
93 data work.combined;
94 set &sheetList.;
ERROR: Variable Cylinders has been defined as both character and numeric.
ERROR: Variable Cylinders has been defined as both character and numeric.
95 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.COMBINED may be incomplete. When this step was stopped there were 0 observations and 14 variables.
I can't provide code for step 4, but i am sure that you can find something useful in the community.
... View more
