02-21-2018 12:03 PM - last edited on 02-21-2018 12:20 PM by Reeza
I'm automating a report with SAS. the final result is an excel file with 8 tabs. In each tab there are 3 fields and only one row.
ex: sheet: paper
fields:total payments, count, percentage
I'm trying to use ODS to combine all 8 sheets into 2 sheets.
how can I get data from multiple sheets with ODS?
the code I'm trying to execute is:
ods tagsets.tableeditor file="c:\temp\temp.js" options(update_target="c:\\temp\\temp.xlsx" output_type="script" sheet_name="payment" pivotdata_caption="payments,counts,percentage" pivotdata="payments,counts,percentage" pivotrow="title"); data _null_; file print; put _all_; run; ods tagsets.tableeditor close;
the results would be something like:
payments counts percentage
paper numbers numbers
epay numbers numbers
02-21-2018 12:31 PM - edited 02-21-2018 12:32 PM
I get the feeling you may be confusing SAS ODS with SAP ODS. In SAP, this stands for Operational Data Store. But in SAS, this is the Output Delivery System. As far as I know (though I don't use ODS very often), output is it's primary intended use, maybe even the only use. Not input.
What pops into my head for input is that SAS can treat Excel Workbooks as a library. If you use a LIBNAME statement such as:
LIBNAME myExcel XLSX path="D:\text.xlsx";
From here, it's particularly easy to read all the data and turn it into whatever you'd like because every sheet from the original data is now accessed just like a data set in a library. To extend your example, if you have paper and maybe an electronic worksheet and you just wanted all the data concatenated, you could use:
DATA work.combined; SET myExcel.paper myExcel.electronic; RUN;
If there are header rows or you need to do some manipulation with the numbers it will take a bit of extra finagling, but once you've got the data you like, then you can start using ODS to send it back to Excel, etc.
02-21-2018 12:54 PM - edited 02-21-2018 01:18 PM
I'm not trying to import any files. I have done my analysis in SAS and export it as an excel file. now, I'm trying to make my file look better. I have 8 different sheets in the excel file, I'm trying to make a table with the data and have 2 sheets instead of 8.
Also, I can't use the data step process because the titles are different in each data set.