topic Re: Detailed setup of Excel output file in SAS in SAS Programming

Detailed setup of Excel output file in SAS

Ivan555 — Sat, 23 May 2020 09:45:01 GMT

Hello!

Could you please tell me how, when exporting data to Excel, to

> add some additional rows/columns in Excel spreadsheet being created

> add some text in specific cells

> add some text formats/background formats/border formats in specific cells

> customize some cells by combining them, also wrapping text, alignment text, rotating text, ...

> do auto alignment of cells and rows, also set the fixed size of rows and columns

I think this all can be solved if we are able to write VB code in SAS and transfer it to Excel.

But I can't find whether it is possible or not.

Could you, please, give me a link on this method(if it exists) or on other methods?

THX!

Re: Detailed setup of Excel output file in SAS

art297 — Sat, 23 May 2020 16:10:23 GMT

The exportxl macro (see: https://github.com/art297/Papers/blob/master/Excelling_to_Another_Level_with_SAS/src/exportxl.sas ) doesn't do all of the things you are trying to do, but does use the approach you were asking about. Hopefully, it will give you an idea regarding how to proceed.

The paper, powerpoint and tipsheet for the macro can be found at: https://github.com/art297/Papers/tree/master/Excelling_to_Another_Level_with_SAS/doc

Art, CEO, AnalystFinder.com

Re: Detailed setup of Excel output file in SAS

Reeza — Sun, 24 May 2020 02:12:49 GMT

> add some additional rows/columns in Excel spreadsheet being created

Not needed if you have Excel Tables that will dynamically change.

> add some text in specific cells

Named Ranges is one option, I prefer to use linked cells in my excel sheet instead.

> add some text formats/background formats/border formats in specific cells

Yes, search traffic lighting

> customize some cells by combining them, also wrapping text, alignment text, rotating text, ...

Some of these yes, not combining cells I believe

> do auto alignment of cells and rows, also set the fixed size of rows and columns

Yes

Some of what you want is available in ODS EXCEL but not all.

DDE allowed you to do most of this but is deprecated. You can use DDE to pass VBA command to Excel though or through a VBS script. If I'm developing these types of reports, I usually make a template and link the cells in Excel rather than worrying about writing to exact cells. I've done all of these, but definitely try and avoid it as much as possible these days.

If you search DDE on lexjansen.com you'll find code examples for most of these actions. The actions need to have been available in Excel 4.0 though if you go DDE. If you go VBS you have more options.

Re: Detailed setup of Excel output file in SAS

s_lassen — Mon, 25 May 2020 18:02:28 GMT

One technique I have used often, not just in SAS, but also in SSIS, is to have a "template" - not a template as Excel understands the word, but an empty workbook that is formatted the way I want it.

You can then have a sheet containing your data, formatted the way you want, and perhaps another sheet or two which present the data in a pivot table or whatever.

With that, you first copy your template workbook to wherever you want the output (using FCOPY or X command), then you assign the new workbook as a SAS libname, and write (SAS datastep, SQL select or whatever) to the sheet where the data is supposed to be.

Hope this is helpful.

Re: Detailed setup of Excel output file in SAS

art297 — Mon, 25 May 2020 19:04:33 GMT

@s_lassen : While I mentioned the exportxl macro simply in the hope that it could provide @Ivan555 with an idea on how to incorporate vb script into SAS code, it includes a way to create a table based on either an existing table or an explicit Excel template.

Art, CEO, AnalystFinder.com