topic Re: ODS TableEditor multiple sheet in ODS and Base Reporting

ODS TableEditor multiple sheet

parmis — Wed, 21 Feb 2018 17:20:57 GMT

hi guys,

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

total

Re: ODS TableEditor multiple sheet

GinaRepole — Wed, 21 Feb 2018 17:32:54 GMT

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.

Re: ODS TableEditor multiple sheet

parmis — Wed, 21 Feb 2018 18:18:13 GMT

gina,

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.