BookmarkSubscribeRSS Feed
parmis
Fluorite | Level 6

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

 

2 REPLIES 2
GinaRepole
SAS Employee

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.

parmis
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 703 views
  • 0 likes
  • 2 in conversation