How to find out all Excel worksheets' names in SAS without using pcfiles or ExcelCS libnames?

0
votes

I have been trying to import a large Excel file in SAS consisting 20 worksheets. I am using following macro for proc import

%macro excel_imp(outds, worksheet);
proc import 
    out=&outds
    datafile= "Z:\temp\sample"
    dbms=XLSX replace;
    sheet="&worksheet";
    getnames=yes;
run;
%mend excel_imp;

%excel_imp(Ds1,Worksheet1);
%excel_imp(Ds2,Worksheet2);

The above code is running fine, but I have to call the macro 20 times with separate worksheet names.

I would like an automated code to identify the worksheet names and then use the macro above. I don't have pcfiles/ExcelCS in my SAS EG, I am using 9.4

Appreciate any help! Thanks.

1
sas

1 Answers

2
votes

Since XLSX clearly works, why not use the XLSX libname. 

libname demo xlsx 'path to xlsx file';

proc copy in=demo out=work;
run;