The SAS Output Delivery System and reporting techniques

suppress output in Excel

Reply
Contributor
Posts: 29

suppress output in Excel

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

Super User
Posts: 24,012

Re: suppress output in Excel

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.
Contributor
Posts: 29

Re: suppress output in Excel

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

Super User
Posts: 13,941

Re: suppress output in Excel

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.

Contributor
Posts: 29

Re: suppress output in Excel

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.  

Super User
Posts: 13,941

Re: suppress output in Excel


@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;
Contributor
Posts: 29

Re: suppress output in Excel

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).

Ask a Question
Discussion stats
  • 6 replies
  • 133 views
  • 0 likes
  • 3 in conversation