Desktop productivity for business analysts and programmers

Concatenating Excel reports in EG

Not applicable
Posts: 0

Concatenating Excel reports in EG


I am generating several reports in EG -and the users wants the output in Excel (because they wants to use the results in other documents/reports).
I then use the Export as step in project with Excel as output.
I it possible to concatinate these reports in one Excel report?
And how do I change the layout on the Excel file that is exported?
Not applicable
Posts: 0

Re: Concatenating Excel reports in EG

Posted in reply to deleted_user
I am unclear on what you want to do.

I presume you export some data to Excel with column names defined from the SAS data source.

How can you concatenate multiple reports unless the column names are the same? Unless you want a series of panels down the page. If that is the case, then I would look at defining named ranges in your target excel spreadsheet, and export to the named range. the issue will then be defining the correct size and position for the range, but since you know more about your data than I can see from way over here [(c) D. Cassell] it may be clearer to you.

If on the other hand you have reports for one of a number of regions and want to assemble similar report structures onto a single page, then look at concatenating your data first and then use by processing to output the data.

With the summary line the Print procedure will produce after each by group, you should get neat data blocks followed by summary lines spaced down the page. Look at the Print procedure syntax, particularly the By, SumBy and PageBy procedure statements.

Kind regards

Not applicable
Posts: 0

Re: Concatenating Excel reports in EG

Posted in reply to deleted_user
Hi, and thanks for you respond!

What I want is a master Excel report that includes data from different dataset in my EG project. If I had HTML reports I could have used the document builder to combine the results from multiple tasks to one single HTML document.

Further I want to format the Excel document in EG (footnote, Headings etc).

Posts: 9,426

Re: Concatenating Excel reports in EG

Posted in reply to deleted_user
I'm not sure what you mean by "master" Excel Report. However, let's say that you have 2 tasks -- one for analysis on the sashelp.class dataset and the second task for analysis on the dataset.

If you create your regular EG output as HTML files, THEN, you can build an document from your 2 tasks (which were run on different datasets).

Now, that you've got an assembled HTML document, you can right-mouse-click on the HTML DOCUMENT in the EG window and choose
Export --> Export Document --> choose Local Computer
and then at the Export window, when you are prompted to give a name, choose a directory for storage and then choose a 2 level name of
exp_all.xls (yes, that's right, give the exported file an extension of .XLS -- do NOT take the default file extension of HTML.)

What will happen is that you are STILL creating an HTML file. (You can prove this by opening the file in Notepad after you finish the Export.) However, it doesn't matter that the file is HTML because Excel can open an HTML file. So when you navigate to the directory where you stored the exported HTML document, you will see your file. (You MUST navigate to the file using Windows Explorer or open the file directly into Excel -- you will NOT be able to click on the Exported file from inside EG).

Why does this work? This works because you are "fooling" the Windows registry into launching Excel when you click on the HTML file. The nice thing about this approach, is that you do get some of the HTML formatting in Excel when you open your document.

If you do not like this approach, then you could export the code from your tasks and create your own HTML file that Excel can open by doing something like this in a code node:
ods html file='c:\temp\samp1_ht4.xls' style=sasweb;
ods html3 file='c:\temp\samp2_ht3.xls' style=sasweb;
ods tagsets.msoffice2k file='c:\temp\samp3_mso.xls' style=sasweb;
ods tagsets.excelxp file='c:\temp\samp4_xp.xls' style=sasweb;

proc means data=sashelp.class min median max;
var height;
class sex;

proc means min median max;
var sales;
class Region;
ods _all_ close;

This example is creating 4 different files using 4 different kinds of markup language. If you open each separate file in Excel, then you will have an idea of how each method's output is treated by Excel.

The files created are:
c:\temp\samp1_ht4.xls = an HTML 4.0 file
c:\temp\samp2_ht3.xls = an HTML 3.2 file
c:\temp\samp3_mso.xls = a Microsoft HTML file
c:\temp\samp4_xp.xls = a Microsoft Spreadsheet Markup Language (XML) file

If you open each file in Notepad, you will see what the underlying markup tags are in each file. Again, I have to stress that giving the files an extension of .XLS is just a convenience -- we are "fooling" Windows into opening Excel.

The above code is doing approximately the same thing that the EG document builder is doing -- it is just happening in code that you can see and manipulate. The good news for you is that you CAN create an HTML document in EG and export it, in its formatted form to Excel. Excel might not respect ALL the HTML formatting, but there are ways around that and Tech Support can help you figure out the best way to accomplish the formatting results you want, once you decide on your approach for how you're going to create the file for Excel.

Good luck,
Not applicable
Posts: 1

Re: Concatenating Excel reports in EG

Posted in reply to Cynthia_sas
You might look at this FAQ; lists several macros that export multiple datasets as individual worksheets in a single workbook.
Posts: 9,426

Re: Concatenating Excel reports in EG

Yes, I have seen those macros. When you said "reports", however, I jumped straight over the idea of using the macros and jumped directly to the idea of creating formatted output with ODS. Glad you found the macros, they are very neat if all you want is the data.
Not applicable
Posts: 0

Re: Concatenating Excel reports in EG


I tried to follow the link you mentioned on your previous post to the FAQ on macros, but the link is now broken. Is says that the page no longer exists.

Are these macros available elsewhere? Thanks in advance.
Ask a Question
Discussion stats
  • 6 replies
  • 3 in conversation