topic Re: Need an output with two separate proc report on each tab in SAS Programming

Need an output with two separate proc report on each tab

KatLinden — Wed, 04 Oct 2023 19:50:41 GMT

ORDER A B

First 10 55

Second 12 34

Third 14 42

Trying to get an Excel output with three tabs: First, Second and Third. I need two proc reports on each tab. First report should display variable A and second report should display variable B. I get six tabs: First, Second, Third, First2, Second2, Third2 with one report on each. What am I doing wrong?

ods excel file="C:\TEST_EXAMPLE.xlsx"
options(sheet_interval='bygroup' sheet_name="#byval1") ;

proc report data=test;
by ORDER;
column A;

run;

proc report data=test ;
by ORDER;
column B;

run;

ods excel close;

Re: Need an output with two separate proc report on each tab

Reeza — Wed, 04 Oct 2023 20:10:26 GMT

When you want two per page, you cannot use BY group processing. You can either manually use start page option or create a macro to process this.

See this tutorial on converting this to a macro.
https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Re: Need an output with two separate proc report on each tab

ballardw — Wed, 04 Oct 2023 20:16:42 GMT

You aren't doing anything "wrong". If you have two procedures that have to run in parallel you have to control things tighter. #Byval is the value in one procedure call. So if you want to call the report so that "First" appears from two different procedures you have to force that behavior. Your example data is likely way simpler than reality otherwise I would ask what is wrong with showing columns A and B in the same table?

You would turn on/off sheet starts to "reset" the output to a new sheet, then use "none" to get related different procedure calls onto the same sheet. Select the specific data with WHERE statements and manually set the names.

ods excel file="C:\TEST_EXAMPLE.xlsx"
options(sheet_interval='none' sheet_name="First") ;

proc report data=test;
Where ORDER='First';
column A;

run;

proc report data=test ;
Where ORDER='First';
column B;
run;

ods excel options(sheet_interval='proc');
ods excel options(sheet_interval='none' Sheet_name='Second');

proc report data=test;
Where ORDER='Second';
column A;

run;

proc report data=test ;
Where ORDER='Second';
column B;
run;

ods excel options(sheet_interval='proc');
ods excel options(sheet_interval='none' Sheet_name='Third');

proc report data=test;
Where ORDER='Third';
column A;

run;

proc report data=test ;
Where ORDER='Third';
column B;
run;


ods excel close;

Possibly you might be able to restructure your data and make a different procedure call but would have to see very complete example of the complete data and reports to tell if that might be possible.

People have used the SAS macro language to do such group processing or similar can be accomplished by using a data set with the items that change, such are the values of Order and using a data step with that data set and CALL EXECUTE statements to write the blocks of values.

Re: Need an output with two separate proc report on each tab

KatLinden — Thu, 05 Oct 2023 01:27:15 GMT

The macro tutorial works fine except for the fact that it creates one file per group instead of separate tabs in the same file.

Re: Need an output with two separate proc report on each tab

Reeza — Thu, 05 Oct 2023 17:18:37 GMT

The example does do that, your use case is slightly different 🙂 You'd need to move the ODS EXCEL creation outside of the macro and then the start page within the macro for your use case. If you post the code, I can help with the changes.