Hi everyone!
Please help me, I want to extracting all sheet name from my excel file and then want to use those sheet name as table names in my script dynamically.
Eg- sheet1. sheet2 are my sheet name
and sheet1 and sheet2 will be also my tablename
libname bjlist xlsx "/mnt/dwhadmin_prod/Work/pensiontjek_05-03-2023.xlsx";
proc contents data=bjlist._all_ noprint out=x;
run;
proc sql noprint;
select distinct memname into :memlist separated by ' ' from x;
quit;
%put &=memlist;
The code above will gve a macro variable containing a list of sheet names.
%let excel= c:\temp\date.xlsx ; *path for inputing excel file;
options validvarname=any validmemname=extend;
libname x xlsx "&excel.";
data _null_;
set sashelp.vtable(keep=memname libname where=(libname='X'));
call execute(catt('proc import datafile="&excel." out=',compress(memname,,'kda'),' dbms=xlsx replace;sheet="',memname,'";run;'));
run;
If it is an XLSX file you can read the XML file where the list of sheets is stored from inside of the XLSX file.
See this old thread: https://communities.sas.com/t5/SAS-Programming/How-to-import-second-sheet-from-Excel-workbook-in-to-...
Or even simpler use the code in this other thread:
data worksheets;
infile "c:\downloads\book1.xlsx" zip member='xl/workbook.xml' recfm=n dsd dlm=' ';
sheet_number+1;
input @'<sheet name=' sheet_name :$32. @@;
run;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.