I have a task.
Import all tabs of an excel workbook (xlsx) into SAS. Tabs can be called anything and number of tabs is variable.
I then need to read out all data sets to recreate the workbook as before, with tabs in their original order.
I have tried reading in excel files using libname statement with xlsx engine which reads them in ok, but does not enable the sheet order to be obtained. I tried using:
proc sql & dictionary.columns
sashelp.vtable
proc contents
but none of these methods help.
@Tom could did it for you .The following code is from Tom which you could know the order of Sheet in a Excel file.
/*******************获得EXCEL中的Sheet名*******************/ filename _wbzip ZIP "c:\temp\file.xlsx" member='xl/workbook.xml'; data sheets ; infile _wbzip dsd dlm=' ' recfm=n ; sheetnum+1; input @'<sheet name=' sheetname :$32. @@; run;
Sure sounds like a task for an Operating System copy file. Why is SAS involved?
What we are doing is doing a validation check of data contained in the sheets. This validation output is exported along with the data sheets of the original file so that we can hyperlink the issues detected. Strange, I know.
Hmmm... if you re-write the data after validation then the data you create is no more really validated.
Would it be possible to just add a new sheet with the validation report to the existing Excel without "touching" the pre-existing data sheets?
What's the OS of your SAS Server? Unix/Linux or Windows?
@Tom could did it for you .The following code is from Tom which you could know the order of Sheet in a Excel file.
/*******************获得EXCEL中的Sheet名*******************/ filename _wbzip ZIP "c:\temp\file.xlsx" member='xl/workbook.xml'; data sheets ; infile _wbzip dsd dlm=' ' recfm=n ; sheetnum+1; input @'<sheet name=' sheetname :$32. @@; run;
Works great! thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: