topic Re: ODS Excel - Multiple PROC REPORTs for Same Worksheet in SAS Programming

ODS Excel - Multiple PROC REPORTs for Same Worksheet

cgsmoak56 — Thu, 24 Dec 2020 18:52:22 GMT

Hi,

I am trying to figure out how to use ODS Excel and two PROC REPORTs to write to the same worksheet. For example,

ods excel file='C:\temp\test.xlsx'

options(sheet_name="dsn" sheet_interval='TABLE' gridlines='ON' flow='DATA');

proc report data=dsn1 nowd
columns VarA VarB VarC;

Define statements

proc report data=dsn2 nowd
columns VarD VarE Var F;

Define statements

Note that the first PROC REPORT uses a dataset called dsn1 and the second PROC REPORT uses a dataset called dsn2. Ideally, I would like for the PROC REPORT using dsn2 to start at row 60 on the worksheet.

Re: ODS Excel - Multiple PROC REPORTs for Same Worksheet

PaigeMiller — Thu, 24 Dec 2020 18:56:12 GMT

If you remove SHEET_INTERVAL='TABLE', your code should work, both PROC REPORT outputs should go in the same worksheet, but you can't control which line it starts on.

Re: ODS Excel - Multiple PROC REPORTs for Same Worksheet

cgsmoak56 — Thu, 24 Dec 2020 19:53:46 GMT

That works, but I have a macro that loops through to create one spreadsheet per topic. So, when I remove the sheet_interval, it puts all of the topics into one spreadsheet. But I think that I have some ideas that I can try out to solve the problem. Thank you for your assistance.