topic Re: Export to excel with special requirements in SAS Programming

Export to excel with special requirements

Ronein — Tue, 10 Apr 2018 21:27:03 GMT

Hello
I have 3 macros that creating 3 types of output tables.
I run these macros for 2 populations
(Symboled by and b)

Step1;Macro1 on population a.
Create 6 tables for population a.
Names of tables:
Tbl_1_1_a Tbl_1_2_a Tbl_1_3_a
Tbl_1_4_a Tbl_1_5_a Tbl_1_6_a

Step2;Macro2 on population a.
Create 3 tables for population a.
Names of tables:
Tbl_2_1_a Tbl_2_2_a Tbl_2_3_a

Step3;Macro1 on population b.
Create 6 tables for population b.
Names of tables:
Tbl_1_1_b Tbl_1_2_b Tbl_1_3_b
Tbl_1_4_b Tbl_1_5_b Tbl_1_6_b

Step4;Macro2 on population b.
Create 3 tables for population b.
Names of tables:
Tbl_2_1_b Tbl_2_2_b Tbl_2_3_b

Step5-Here is my question.
I want to export the table to one excel file with following requirements:
Name of sheet1 will be output1.
In sheet1 there will be tables Tbl_1_1_a Tbl_1_2_a Tbl_1_3_a
Tbl_1_4_a Tbl_1_5_a Tbl_1_6_a
down each other
and parallel to them will be tables
Tbl_1_1_b Tbl_1_2_b Tbl_1_3_b
Tbl_1_4_b Tbl_1_5_b Tbl_1_6_b
also down each other .

Name of sheet2 will be output2.
In sheet2 there will be tables
Tbl_2_1_a Tbl_2_2_a Tbl_2_3_a down each other
And parallel to them will be tables
Tbl_2_1_b Tbl_2_2_b Tbl_2_3_b
Can anyone send a code that can do step5 of export to excel??
I dont have any requirement regarding colors and styles of tables .

Thank you so much

Re: Export to excel with special requirements

Ronein — Tue, 10 Apr 2018 21:28:12 GMT

Sorry.
I have 2 macros that creating 2 types of output tables.
I run these macros for 2 populations
(Symboled by a and b)

Re: Export to excel with special requirements

Reeza — Tue, 10 Apr 2018 21:46:54 GMT

AFAIK there isn't an easy way to do this at this point of time - which is why you've asked the same question three times and not received a response. The closest option that I'm aware of uses ODS HTML, but that will not generate a native Excel file and does not support multiple sheets easily.

The other two options, the custom macro written by some users here and/or DDE. Those are the order I'd suggest those in as well.

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Given your post, the macro above seems like your best option.

Good Luck.

Re: Export to excel with special requirements

art297 — Wed, 11 Apr 2018 14:11:55 GMT

@Ronein: I agree with @Reeza, but suggest that you use the most recent version of that macro. You can find it at:

https://github.com/FriedEgg/Papers/tree/master/Excelling_to_Another_Level_with_SAS

Art, CEO, AnalystFinder.com

Note: Corrected link. Code is under SRC, while all documentation is under doc

Re: Export to excel with special requirements

Ronein — Wed, 11 Apr 2018 04:15:26 GMT

Hi Reeeza
Thanks for your reply.
I opened the link you sent but can't see there any code.
Can you please tell me where to go in this link ?

Re: Export to excel with special requirements

Ronein — Wed, 11 Apr 2018 04:16:43 GMT

Hi art297
Thank you very much.
Can you tell me please which file to open in your link ?

Re: Export to excel with special requirements

art297 — Wed, 11 Apr 2018 12:58:56 GMT

Oops! The code obviously wasn't downloaded as yet to the github page, and the page owner is currently at SGF. As such, here is where you can get it: http://www.sascommunity.org/wiki/Excelling_to_Another_Level_with_SAS

Art, CEO, AnalystFinder.com

Re: Export to excel with special requirements

DWilson — Fri, 01 Feb 2019 17:53:04 GMT

Nifty macro. I note that it only works for Windows. If you have SAS on a non-windows operating system, you could create your own macro as we did. It uses SAS IML to interface with R (pass SAS data sets and R code) to call 3rd party R packages (either openxlsx or xlConnect) to allow us to write to cells without having to pre-specify ranges in advance. It also allows us to use pre-specified excel templates with formatting and whatnot applied.