Getting Started with PROC REPORT
The session covers PROC REPORT and its statements. It
- Describes the COLUMN and DEFINE statement.
- Discusses how compute blocks can be used.
- Examples demonstrate the ORDER and GROUP usage, the ORDER= option, and SPANROWS.
- Additional examples demonstrate how to create a new column for the report and how to apply style overrides.
Here are some highlighted questions from the Q&A segment held at the end of the session for ease of reference.
Might be beyond this subject... When using ODS Excel and Proc Report, I have issues with the auto row height. Sometimes it is too small and sometimes it is too high. Is there a way around this?
Yes, that is a little beyond the scope of the PROC REPORT Getting Started Ask the Expert. And I’d really need to see what you are doing. I recommend opening a track with SAS Technical Support when you have this issue again. Be sure to include the version of SAS that you are using and an example of the problem so the consultant can clearly see the issue.
What are the deciding factors when deciding to use proc report vs proc tabulate?
If you are comfortable using PROC TABULATE and you like the way the results look then use that. If you need control over the header section of the report, need to add text, add a new column, or in some way have two columns interact with one another then you need to use PROC REPORT.
I will admit though, that generating percentages is a whole lot easier with PROC TABULATE. If you need percentages you TABULATE, generate an output data set, then use REPORT to generate the final report.
There are a number of papers and community postings comparing the two procedures. You might try Googling those. There is this paper
SAS® Reporting 101: REPORT, TABULATE, ODS, and Microsoft Office
How do proc tabulate and proc report compare?
PROC TABULATE and PROC REPORT both calculate simple statistics, transpose on the fly, and apply row and cell level style attributes.
PROC REPORT lets you add a line of text in the middle of a table. It also can add, subtract, multiply, and divide values across columns. Within a compute block you can use conditional logic like what was shown in one of the demos during the webinar.
PROC TABULATE does an amazing job at calculating percentages without too much work. It can also get summary values at different combinations of CLASS variables, something PROC REPORT cannot do.
In older SAS versions there were a underline and double underline options. How do you do these with the current version of SAS?
The underline and double underline options are still available in PROC REPORT. However, those options are only applicable to the ODS Listing destination. A lot of reports these days are sent to RTF, PDF, HTML, or Excel where those options are not valid.
I recommend that you look at the Style Attributes Table for the border control attributes. You can use those to add borders. Depending on the destination you might be able to get something other than a solid line.
Do you have time to demo a calculated percentage using a compute block?
I’m sorry we could not get to demoing a calculated percentage using a compute block within the webinar. I recommend that you look at these SAS Notes for examples of calculating percentages with PROC REPORT.
Sample 49390: Calculate percentages within groups using PROC REPORT
Sample 43091: Calculate percentages under an ACROSS variable using PROC REPORT
Sample 63064: Add a row with percentages to PROC REPORT output
How can this report then be published? Does it output to some kind of format that can then be delivered?
You can choose the document format that works best for your situation. There is an ODS destination for creating HTML, PDF, RTF, Excel, and PowerPoint formats.
SAS® 9.4 Output Delivery System: User’s Guide
Can we export this file in excel format with different tabs for each month?
Yes, PROC REPORT can be exported to Excel. The SHEET_INTERVAL suboption in the ODS destination for Excel controls when a new tab is generated. You can find an example of creating a new tab for each month in this paper:
How to add the pagination on the bottom of each page, and also how to deal with text wrapping to the next page, if the value of one column is too long?
The answer to this dependent upon which ODS destination you are using. RTF and PDF, and PowerPoint to an extent, are the only true paging destinations. The other destinations are not limited to the size of a piece of paper.
That said, the way to have control of where a table breaks is to use the PAGE option in the BREAK statement and the DEFINE statement.
I recommend you look through the information is this SAS Note
How can we calculate a percent of column? More specifically a percent of the column that omits the missing values.
I recommend that you look at these SAS Notes for examples of calculating percentages with PROC REPORT.
Sample 49390: Calculate percentages within groups using PROC REPORT
Sample 43091: Calculate percentages under an ACROSS variable using PROC REPORT
Sample 63064: Add a row with percentages to PROC REPORT output
If the above samples do not address your question about omitting missing values, then I recommend opening a track with SAS Technical Support. Be sure to include the version of SAS that you are using and an example of the problem so the consultant can clearly see the issue.
What other function are available besides sum i.e. product.sum?
Here is a list of Statistics That Are Available in PROC REPORT.
Option SPANROWS is not working in ODS RTF output, is it any way to make it work?
SPANROWS should work with ODS RTF. If you find it is not doing what you expect it to I recommend opening a track with SAS Technical Support. Be sure to include the version of SAS that you are using and an example of the problem so the consultant can clearly see the issue.
How do you compute a DISTINCT N? Let's say you have a sub-product variable that contains varieties of beds, chairs, etc. How do you get a distinct count of those sub-products?
I think you mean want to know that there are four distinct values of product (bed, chair, desk, table). PROC REPORT cannot do this without preprocessing the data. The N statistics in PROC REPORT will give you the frequency count of each level but not the distinct count of all levels.
You can use PROC FREQ with the NLEVELS option or the COUNT DISTINCT function in PROC SQL.
Using a compute block can you combine multiple summaries in one column? Ex: (Min, Max)
Yes, you can use an assignment statement to combine the values of multiple statistics into on column. For example:
proc report data=sashelp.class;
column name height height=hmin combo;
define height / max;
define hmin /min;
define combo / computed;
compute combo /char length=12;
combo = put(hmin,4.1) || ', ' || put(height.max,4.1) ;
endcomp;
run;
What does order=data mean?
When you specify ORDER=DATA, the ordering pattern for GROUP, ORDER, and ACROSS variables in PROC REPORT is established as the order of appearance across all observations in the data set.
For a full explanation and example, refer to this SAS Note
How to calculate the average of month instead of sum of months using compute?
In the demos, the actual and predict variables defaulted to the sum statistic. You could add a DEFINE statement for your analysis variables and specify the mean statistic if you want the average.
A compute block is not necessary unless you are overriding an existing value in a column or creating a new column.
Does the summary function sum across all records even if the values are missing?
By default, missing values are excluded from the calculation of statistics.
Can you do a sample array?
Please refer to this SAS Note for an example of using an array in PROC REPORT.
Sample 43765: How to use an array in a COMPUTE block with PROC REPORT
How do I sort my data based on one of the "across" data point?
The ORDER= option in the DEFINE statement is valid for ACROSS variables. You can use that to order the columns created by the values of that variable.
Can we underline headers in plain ODS RTF without using TAGSET without creating a blank row?
The border style attributes are valid in ODS RTF. They will add borders to the cell without adding a blank row. I recommend that you look at this SAS Note for an example.
Customize header borders in ODS PDF and ODS RTF output with PROC REPORT
How do I sort the table by the sum?
A PROC REPORT table cannot be sorted by an analysis variable. You can only sort by a GROUP or ORDER variable. If you need to table sorted by a summarized value it is a two-step process. You can get the summary from a PROC MEANS step, PROC REPORT, PROC TABULATE, whatever your favorite procedure is. Then you can output it with PROC REPORT and make the summarized variable a GROUP or ORDER variable.
Is it possible to merge header cells?
No. PROC REPORT will merge spanning headers. There is not syntax to force headers to merge. You cannot merge vertically. If it is important to have merged header cells, I recommend that you check out the Report Writing Interface.
How do you add a blank row between groupings?
When sending the report to an ODS destination such as HTML, PDF, or RTF you add a blank line with a LINE statement. The syntax is:
compute after month;
line ' ';
endcomp;
I have a categorical variable but not all of the possible values are present in the data set. How do I include that category in the table?
You make all of your categories show up in PROC REPORT, either a GROUP, ORDER, or ACROSS variable. The key is to create a format with all of your possible values. Then you need to use the PRELOADFMT option in the DEFINE statement. These two SAS Notes demonstrate how to do this.
Sample 39648: How to create a report using PROC REPORT to show all the possible variable values
Sample 45458: Customize the order of GROUP or ACROSS variable values in PROC REPORT
Can I apply conditional formatting based on the value of another variable?
Yes, you can. The key is that PROC REPORT works from left to right based on the COLUMN statement so you need to make sure the variable in the COMPUTE statement is the correct on so that all values are available. Also, in the CALL DEFINE statement the first argument should be the name of the column you want to change.
This paper contains examples of applying conditional logic. The REPORT Procedure: A Primer for the Compute Block
How do I get the value of a GROUP or ORDER variable to be written on every row instead of seeing blank cells?
In a compute block you can create a temporary variable. It is a variable that is not on your COLUMN statement and will not be part of your output, but it can hold a value. Then on other rows where you need the value to be, you do an assignment statement. It is a trick with a compute block and a temporary variable. This SAS demonstrates how to get GROUP values to be written out on every single row.
Usage Note 24322: Display the ORDER or GROUP variable value on every row with PROC REPORT
Spacing option not working for me for few columns. Does it gets affect with any other options?
The SPACE= option is only valid in the Listing destination.
If we order by two variables in a proc report statement but only group by one, does it cause the values of the grouped variable to no longer span each row?
A second ordering variable will not prevent the first variable from spanning each row. However, a GROUP or ORDER variable will repeat its value when there is a GROUP or ORDER variable to the left with a shown value.
Is it possible to label the summary row with a different name than the value of the group variable its summarizing?
Yes, it is possible. The examples on page 9 of this paper demonstrate how to do this.
The REPORT Procedure: A Primer for the Compute Block
How do you keep numeric variables from being treated as analysis variables and summed? I sometimes want to list them.
In the DEFINE statement specify a usage of DISPLAY.
How would you auto adjust column width?
The width of the column is dependent upon the ODS destination you are sending the report to, the column header, the font size, and a number of other settings. You can specify that the same width should be used for all columns via the STYLE option in the PROC REPORT statement.
proc report data=sashelp.prdsale style(column)=[width=1in];
What are the rules for column widths in regards to fitting all columns on the same page?
There are no specific rules for getting columns to fit on the same page. The number of columns that fit on a page is determined by the ODS destination you are sending the report to, the column header, the font size, and a number of other settings.
Is it possible to use conditional language for a line statement?
Yes, a LINE statement value can be conditionally printed by using a text string and adjusting the format length. This SAS demonstrates the technique.
Sample 37763: Conditionally print a LINE statement in PROC REPORT
Can you add text (not from a variable) at the end of the table, either through a line statement or other means?
Yes, text can be placed at the end of a table by using a LINE statement in a compute after block.
compute after;
line 'my text';
endcomp;
Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q/A, slides and recordings from other SAS Ask the Expert webinars. To subscribe, select Subscribe from the Options drop down button above the articles.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
