Need an output with two separate proc report on each tab

[KatLinden]

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;

[Reeza · Accepted solution]

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

[KatLinden]

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

[Reeza]

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.

[ballardw]

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.