Before you try to figure out how to do something 100 times you need to figure out how to do it once.
To read an XLSX file you can use the XLSX libname engine. That will give you access to every worksheet in the file as it it was a dataset.
To keep only one observation from an XLSX file you can use the OBS system option in combination with PROC COPY. Let's try it out. First let's make an XLSX file with two sheets. Then try copying out the sheets.
filename xx temp;
libname xx xlsx "%sysfunc(pathname(xx))";
data xx.male ;
set sashelp.class;
if sex='M' ;
run;
data xx.female ;
set sashelp.class;
if sex='F' ;
run;
options obs=1;
proc copy inlib=xx outlib=work; run;
options obs=max;
See how well it worked.
714 libname xx xlsx "%sysfunc(pathname(xx))";
NOTE: Libref XX was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\Users\ABERNA~1\AppData\Local\Temp\1\SAS Temporary Files\_TD14144_AMRAPY3WVP0VKU0_\#LN00058
715 data xx.male ;
716 set sashelp.class;
717 if sex='M' ;
718 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set XX.male has 10 observations and 5 variables.
NOTE: The export data set has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
719 data xx.female ;
720 set sashelp.class;
721 if sex='F' ;
722 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set XX.female has 9 observations and 5 variables.
NOTE: The export data set has 9 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
723
724 options obs=1;
725 proc copy inlib=xx outlib=work; run;
NOTE: Copying XX.FEMALE to WORK.FEMALE (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set XX.FEMALE.
NOTE: The data set WORK.FEMALE has 1 observations and 5 variables.
NOTE: Copying XX.MALE to WORK.MALE (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set XX.MALE.
NOTE: The data set WORK.MALE has 1 observations and 5 variables.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
726 options obs=max;
If you need to keep track of what sheets were in each particular file you might need to work harder.
For XLS files it will be harder to read more than just the first sheet.
Do you know if any of the XLS files have more than one sheet?
Also PROC IMPORT for XLS (and XLSX files) do not seem to honor OBS system option. So you will need to add another step to truncate the dataset after creating it. Again let's run a test.
filename xx temp;
proc export data=sashelp.class file=xx dbms=xls ; run;
proc import file=xx dbms=xls out=class replace; run;
data class;
set class(obs=1);
run;
Results
740 filename xx temp;
741 proc export data=sashelp.class file=xx dbms=xls ;
741 ! run;
NOTE: The export data set has 19 observations and 5 variables.
NOTE: "XX" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
742 proc import file=xx dbms=xls out=class replace;
742 ! run;
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 19 observations and 5 variables.
NOTE: WORK.CLASS data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
743 data class;
744 set class(obs=1);
745 run;
NOTE: There were 1 observations read from the data set WORK.CLASS.
NOTE: The data set WORK.CLASS has 1 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
