topic Re: import multi tab excel file & export with tabs in same order in SAS Programming

import multi tab excel file & export with tabs in same order

kalbo — Fri, 26 Aug 2022 15:06:50 GMT

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.

Re: import multi tab excel file & export with tabs in same order

ballardw — Fri, 26 Aug 2022 15:10:22 GMT

Sure sounds like a task for an Operating System copy file. Why is SAS involved?

Re: import multi tab excel file & export with tabs in same order

kalbo — Fri, 26 Aug 2022 15:14:39 GMT

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.

Re: import multi tab excel file & export with tabs in same order

Patrick — Sat, 27 Aug 2022 01:38:51 GMT

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?

Re: import multi tab excel file & export with tabs in same order

Ksharp — Sat, 27 Aug 2022 11:53:15 GMT

@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;

Re: import multi tab excel file & export with tabs in same order

kalbo — Tue, 30 Aug 2022 13:25:12 GMT

Works great! thanks