BookmarkSubscribeRSS Feed
Batman
Quartz | Level 8

I'm looking for a way to suppress output from procedures used to generate ODS output in Excel.   I want to use the ODS output generated in rows 41 and 66 below in separate worksheets in Excel.   However, I want to avoid also printing the output from the means procedures.   I'm able to exclude the first because it occurs before I invoke the ods Excel command.   However, that isn't always convenient.   In this case, I generate run a second means procedure and that is included in the Excel output.   Is there a way to suppress it or keep it from being sent to the Excel file.

 

 

26 GOPTIONS ACCESSIBLE;

27 options validvarname=v7;

28

29 /*create sample data set*/

30

31 data a;

32 input x 1. y 1.;

33 datalines;

NOTE: The data set WORK.A has 2 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.03 seconds

 

36 ;

37

38 /*First ods output, before Excel file creation*/

39 proc means data=a stackods mean;

40 var x;

41 ods output summary=meanval;

42 run;

NOTE: The data set WORK.MEANVAL has 1 observations and 2 variables.

NOTE: There were 2 observations read from the data set WORK.A.

NOTE: The PROCEDURE MEANS printed page 1.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.08 seconds

cpu time 0.07 seconds

 

43

2 The SAS System 14:49 Friday, July 27, 2018

44 ods excel file='s:\Output\test.xlsx'

45 options (embedded_titles='yes' embedded_footnotes='yes' sheet_name='Sheet1'

46 sheet_interval='none');

47

48 /*desired output for Sheet1*/

49 proc print data=meanval;

50 run;

NOTE: There were 1 observations read from the data set WORK.MEANVAL.

NOTE: The PROCEDURE PRINT printed page 2.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.04 seconds

cpu time 0.04 seconds

 

51

52 ods excel options(sheet_interval='output');

53

54 /*Necessary to get SAS to output multiple worksheets to an XLSX file*/

55 ods exclude all;

56 data _null_;declare odsout t();

57 run;

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

 

58 ods select all;

59

60 ods Excel options(sheet_name = "Sheet2" sheet_interval='none');

61

62 /*I can't say "no print" and still get ODS output, so this procedure

63 also generates output that I don't want to see*/

64 proc means data=a stackods mean;

65 var y;

66 ods output summary=meanval;

67 run;

NOTE: The data set WORK.MEANVAL has 1 observations and 2 variables.

NOTE: There were 2 observations read from the data set WORK.A.

NOTE: The PROCEDURE MEANS printed page 3.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.07 seconds

cpu time 0.07 seconds

 

68

69 /*desired output for Sheet2*/

70 proc print data=meanval;

71 run;

NOTE: There were 1 observations read from the data set WORK.MEANVAL.

NOTE: The PROCEDURE PRINT printed page 4.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

 

3 The SAS System 14:49 Friday, July 27, 2018

 

72

73 ods excel close;

NOTE: Writing EXCEL file: s:\Output\test.xlsx

74

6 REPLIES 6
Reeza
Super User
I see you have one ODS EXCLUDE. Once you know the table names you can use ODS EXCLUDE TABLENAME to exclude it or the opposite, use ODS SELECT to control only what you want to appear.
Batman
Quartz | Level 8

Where would I put the ODS Select statement?   Before or after the Means Procedure?

ballardw
Super User

Generate any data sets to be printed or otherwise displayed outside of the ODS display or file "sandwich".

 

It general it is often a very good idea to generate the data separate from the report. Sometimes the data could take a fair amount of time to run the data portions and there is no real good reason to rerun some model or other summaries once they are correct. Then use the ODS to do the display. That way you need not rerun things if you just need to change one style element in one proc print.

 

Also you might be able to create one summary for multiple variables and use WHERE statements in proc print to display the desired portion as needed.

 

I have one report where I summarize a data set with 8 class variables and 30+ other variables. Then have a BUNCH of proc print or tabulate calls to display the selected combinations of data and summary statistics. The only thing appearing in the ODS sandwich are the print calls and title statements.

Batman
Quartz | Level 8

The example I gave is dumbed down version of a much bigger and more complicated program.   Basically, I need to revise the output of a number of means procedures and combine them into a unified dataset that I then print out.   I'm trying to avoid printing the same data twice.  

ballardw
Super User

@Batman wrote:

The example I gave is dumbed down version of a much bigger and more complicated program.   Basically, I need to revise the output of a number of means procedures and combine them into a unified dataset that I then print out.   I'm trying to avoid printing the same data twice.  


Please explain to me why combining data to print as stated above has anything to do with having to run proc means inside your ODS sandwich?

 

And perhaps what you want isn't actual proc means / proc print but could be using another report procedure such as Tabulate or Report. These do summaries and create formatted output with many more controls than proc means. Especially if all your data comes from one initial data set.

Such as

proc tabulate data=sashelp.class;
   var height weight;
   table height weight,
         mean
         /box='Variable'
   ;
run;
Batman
Quartz | Level 8

Originally I did want to do this in the Report or Tabulate procedures.   However, proc report doesn't allow for the "stacking" of variables vertically.    Proc Tabulate permits allows for stacking, but it doesn't allow column operations (subtracting the value of one column from another).

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1415 views
  • 0 likes
  • 3 in conversation