ODS Excel MultiSheet Workbook with (unfortunately) VBA Macro Formatting

[GBL__]

Hello all,

So my group is switching over to SAS for their analysis and reporting, which I am very excited about, because I will be writing basically everything from scratch (yay!).  However, I am running in to a couple issues implementing enhancements that senior management have requested.

So far I have created the framework for a multi sheet workbook to be outputted into Excel that has been formatted and looks great, imo.  But, we are going to be adding "TextBoxes" on all sheets after the Trending worksheets in the workbook.  I have created a simple VBA macro to accomplish this, and can share that code with the analysts in my group to add to their PERSONAL.XLSB macro worksheet so it can be called anytime.

First Question:  Is there a way for me to add a TextBox into my output directly from SAS?  I am trying to move away from VBA, which our group's previous reporting relied heavily upon.

Next, there needs to be a few sheets created/added to the beginning of the workbook, which will serve as a Summary and Data Observation tabs.  I would like these worksheets to be pre-formatted so the overall look of the workbook is consistent, but I am not sure how to implement this.

Second Question:  Is it possible to send my output to an Excel Template that has those 2/3 beginning sheets already included, and just insert the remaining 10-20 sheets after those?  I know that there is the %exportxl macro floating around out there, but would still like to ask (even though it has probably been asked a million times, sorry).

Is it possible for me to create a "report template" via PROC REPORT or a DATA _NULL_ step to create the outline for those first couple sheets, and then fill in the sections from our internal databases and the monthly external data sets we receive?

As of now, the plan is to run an X Command at the end of the program to open to Workbook, and then hopefully figure out how to transfer my VBA code to a VBScript so I can then call that from within SAS.  But I would rather just use SAS for everything.

None of the analysts in my group have any experience with SAS, so they will be using EG 7.15 for running these reports and performing ad hoc analysis (with SAS 9.4 TS1M3), and for the time being we will not be on the SAS GRID.

I apologize if I am not being clear in my questions or not supplying enough information.  Thank you in advance for any assistance!

[SASKiwi]

ODS Excel creates XML-based workbooks behind the scenes. The limitation of this approach is it can't be used to modify existing workbooks only create new ones.

If you wish to modify existing workbooks then you will need to switch to using either PROC EXPORT using DBMS EXCEL (or XLS, XLSX) or the LIBNAME statement using the EXCEL (or XLS, XLSX) engine. The downside is you lose most of the ODS formatting options. If formatting is important then you can write your data to a "vanilla" worksheet, then use formulas or macros to copy the data to your formatted template.

There is a third option and that is the SAS Add-in to MS Office. Here you connect to SAS from Excel and pull the required SAS data into Excel rather than push the data to Excel from SAS. In essence Excel is your client tool for reading SAS data.

[GBL__]

Good Morning,

Thank you for your reply!

As of now, the first part of the program queries our internal database using SQL pass-through to create temporary tables containing average trending data for the previous 18 months.  Then, we take that temporary local SAS table and add formats and labels (switching our DB "char" datetimes to mmddyy10. and numeric variables to dollar18.2 etc.).  We also query the current month's data file, which is "local", and append the two to create a rolling average trend table.

The next section of the program PROC PRINTS the above mentioned tables utilizing ODS EXCEL (with many options and sub-options) to further format the appearance of the outputted .xlsx report.  Additionally in this section, we use PROC SQL to query the current months data file to create additional worksheets that come after the 10 or so trending worksheets to highlight specific issues in the current months data file.

So, the avenue I am using now to send output to Excel is PROC PRINT, and I am not sure that this can be accomplished via PROC EXPORT.

[ballardw]

---

@GBL__ wrote:

Hello all,

So my group is switching over to SAS for their analysis and reporting, which I am very excited about, because I will be writing basically everything from scratch (yay!).  However, I am running in to a couple issues implementing enhancements that senior management have requested.

So far I have created the framework for a multi sheet workbook to be outputted into Excel that has been formatted and looks great, imo.  But, we are going to be adding "TextBoxes" on all sheets after the Trending worksheets in the workbook.  I have created a simple VBA macro to accomplish this, and can share that code with the analysts in my group to add to their PERSONAL.XLSB macro worksheet so it can be called anytime.

First Question:  Is there a way for me to add a TextBox into my output directly from SAS?  I am trying to move away from VBA, which our group's previous reporting relied heavily upon.

What exactly is the purpose of the "textboxes"? Are these supposed to contain calculations from the exported data, explanatory notes, used for data entry, insert text into the body of graph? Where does the content of the text box come from?

There are lots of ways to write static text with anything from Footnote to ODS Text or Procs Odstext or Odslist or the data step report writing interface.

So perhaps some more detailed description of what these text boxes are supposed to contain is in order.

---

Second Question:  Is it possible to send my output to an Excel Template that has those 2/3 beginning sheets already included, and just insert the remaining 10-20 sheets after those?  I know that there is the %exportxl macro floating around out there, but would still like to ask (even though it has probably been asked a million times, sorry).

Is it possible for me to create a "report template" via PROC REPORT or a DATA _NULL_ step to create the outline for those first couple sheets, and then fill in the sections from our internal databases and the monthly external data sets we receive?

---

Personally I am not a big fan of Excel templates as things can get buried and when an external change is made to the report requirement finding all of the not-very-easy to find items that may be in the template takes a lot of time to adjust. I would prefer to make an entire report table (or 20 or 100) from SAS if at all practical.

Generally I don't see many things that Excel does that can't be done in SAS though sometimes you need to modify the back end data a bit better.

If you could post examples of the kinds of things you actually need, assuming nothing proprietary, you might get targeted answers.

[GBL__]

A little business background:  Our data analysts produce these reports for other analysts to use as a guide for an on-site client review, and also as a summary of our clients data file(s).  Upper management then uses both the aforementioned reports as part of a "Global" report that includes oh higher level financial, market, and risk analysis.

So, the purpose of the text boxes is to have an area where the data analyst/report creator can write up a summary of what that tab represents/contains (ie. 154 records noted as having modified contracts, and roughly 2/3 of these are repossessed, etc etc etc) and highlight specific areas of concern (3 records with possible balloon payments, on-site analyst should confirm at client HQ etc etc etc), and have a corresponding area where the other on-site analysts can write there response/follow-up.

Here is a screenshot of what I have now, using VBA:

Again, I am looking for a way to implement this via SAS, rather than VBA.

Thank you ballardw for your help/feedback, it is much appreciated

[DWilson]

I suggest looking into installing R and the openxlsx and/or xlsconnect packages if you want to use pre-defined excel templates.

You can call R from SAS using PROC IML;

I'm trying to find a link to the SESUG paper a colleague of mine wrote that provides examples. When I find it, I'll update the post with a link.

Here it is:

https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-109_Final_PDF.pdf

[GBL__]

Wow. And here I thought I have read and/or searched every SUG paper for information that could potentially help me.

Thank you for sharing, I will definitely look more in to this.

Thank you!

[DWilson]

---

@GBL__ wrote:
Wow. And here I thought I have read and/or searched every SUG paper for information that could potentially help me.

Thank you for sharing, I will definitely look more in to this.

Thank you!

---

This page has some discussion about using one of those R packages to add text boxes to .xls files. You can use the same approach with SAS and Proc IML calling R.

https://community.rstudio.com/t/best-r-package-to-create-textboxes-in-ms-excel/947

If those R packages don't have the features you need, you could try using Python with SAS.

https://xlsxwriter.readthedocs.io/

It isn't integrated with SAS like R so you'd have to create what you want to write to excel and save it in a format that python can process. then call python (using, for example, the python modue linked above) with, say, and "x" command from within your SAS program. That call would run a python script that would put your content into a .xlsx file. You also need to make sure that, for this module, the BSD license works for your company. Anyway, something else to consider.

[GBL__]

I feel like as of now I am stuck with using VBA.  Hopefully in the future the ODS Excel destination with be expanded.

Thanks everyone for your responses!