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;
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: