Proc Report: A Step-by-Step Introduction to PROC REPORT and advance techniques

1. Proc Report: A Step-by-Step Introduction to PROC REPORT and advance techniques Santosh Bari, eClinical Solutions Proc Report - HASUG by Santosh Bari

2. What Does the REPORT Procedure Do? The REPORT procedure combines features of the PRINT, MEANS, and TABULATE procedures with features of the DATA step in a single report-writing tool that can produce a variety of reports. You can use PROC REPORT in three ways: • Non-windowing environment • an interactive report window environment with a prompting facility. • an interactive report window environment without the prompting facility It can produce detailed report or summary report using the proc report. Proc Report - HASUG by Santosh Bari

3. Concepts: REPORT Procedure To design the layout, ask yourself the following types of questions: • What do I want to display in each column of the report? • In what order do I want the columns to appear? • Do I want to display a column for each value of a particular variable? • Do I want a row for every observation in the report, or do I want to consolidate information for multiple observations into one row? • In what order do I want the rows to appear? Proc Report - HASUG by Santosh Bari

4. When you understand the layout of the report, use the COLUMN and DEFINE statements in PROC REPORT to construct the layout. • The COLUMN statement lists the items that appear in the columns of the report, describes the arrangement of the columns, and defines headings that span multiple columns. A report item can be 􀀀 a data set variable 􀀀 a statistic calculated by the procedure 􀀀 a variable that you compute from other items in the report. • Omit the COLUMN statement if you want to include all variables in the input data set in the same order as they occur in the data set. Proc Report - HASUG by Santosh Bari

5. Syntax: REPORT Procedure PROC REPORT <option(s)>; BREAK location break-variable</ option(s)>; BY <DESCENDING> variable-1 <…<DESCENDING> variable-n> <NOTSORTED>; COLUMN column-specification(s); COMPUTE location <target> </ STYLE=<style-element-name> <[style-attribute-specification(s)]>>; LINE specification(s); . . . select SAS language elements . . . ENDCOMP; COMPUTE report-item </ type-specification>; CALL DEFINE (column-id, ’attribute-name’, value); . . . select SAS language elements . . . ENDCOMP; DEFINE report-item / <usage> <attribute(s)> <option(s)> <justification> <COLOR=color> <’column-header-1’ <…’column-header-n’>> <style>; FREQ variable; RBREAK location </ option(s)>; WEIGHT variable; Proc Report - HASUG by Santosh Bari

6. PROC REPORT <option(s)>; Example: proc report data=datrpt.test split = '@' headline headskip missing nowindows; Option Task DATA= Specify the input data set OUT= Specify the output data set WINDOWS | NOWINDOWS Select the interactive report window or the nonwindowing environment LS= Specify the length of a line of the report PS= Specify the number of lines in a page of the report. SPLIT= Specify the split character CENTER | NOCENTER Specify whether to center or left-justify the report and summary text MISSING Consider missing values as valid values for group, order, or across variables SPACING= Specify the number of blank characters between columns FORMCHAR <(position(s))>=’formatting-character(s)’ defines the characters to use as line-drawing characters in the report. Proc Report - HASUG by Santosh Bari

7. HEADLINE underlines all column headings and the spaces between them at the top of each page of the report. Default: hyphen (-) HEADSKIP writes a blank line beneath all column headings (or beneath the underlining that the HEADLINE option writes) at the top of each page of the report. Restriction: This option has no effect on ODS destinations other than traditional SAS monospace output proc report data=grocery nowd headline headskip ls=66 ps=18; proc report data=test split = '@' headskip missing nowindows; procreport data=grocery nowd headline formchar(2)="~" panels=99 pspace=6 ls=64 ps=18; Proc Report - HASUG by Santosh Bari

8. BREAK location break-variable</ option(s)>; Produces a default summary at a break (a change in the value of a group or order variable). The information in a summary applies to a set of observations. The observations share a unique combination of values for the break variable and all other group or order variables to the left of the break variable in the report. Example: break after sector / ol summarize suppress skip; Sales Figures for Northern Sectors Sector Manager Sales ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ Northeast Alomar 786.00 Andrews 1,045.00 ƒƒƒƒƒƒƒƒƒƒ $1,831.00 Northwest Brown 598.00 Pelfrey 746.00 Reveiz 1,110.00 ƒƒƒƒƒƒƒƒƒƒ $2,454.00 Combined sales for the northern sectors were $4,285.00. Proc Report - HASUG by Santosh Bari

9. location controls the placement of the break lines and is either AFTER places the break lines immediately after the last row of each set of rows that have the same value for the break variable. BEFORE places the break lines immediately before the first row of each set of rows that have the same value for the break variable. break-variable is a group or order variable. The REPORT procedure writes break lines each time the value of this variable changes. Options DOL Double overline each value DUL Double underline each value OL Overline each value, UL - Underline each value PAGE Start a new page after the last break line SKIP Write a blank line for the last break line SUMMARIZE Write a summary line in each group of break lines SUPPRESS Suppress the printing of the value of the break variable in the summary line and of any underlining or overlining in the break lines in the column containing the break variable Proc Report - HASUG by Santosh Bari

10. COLUMN Statement column-specification(s) is one or more of the following: 􀀀 report-item(s) 􀀀 report-item-1, report-item-2 <. . . , report-item-n> 􀀀 (’header-1 ’< . . . ’header-n ’> report-item(s) ) 􀀀 report-item=name • where report-item is the name of a data set variable, a computed variable, or a statistic column manager department sales; • Tip: You can use parentheses to group report items whose headings should appear at the same level rather than stacked one above the other. Proc Report - HASUG by Santosh Bari

11. Example1 Treatment1 Treatment2 (N=61) (N=69) ---------------------------------------------------- ------------------------------------------------------ << “—” n assessed Responders Non-Responders n assessed Responders Non-Responders ---------------------------------------------------------------------------------------------------------------------------------------- <<headline WEEK 1 59 17 (28.8%) 42 (71.2%) 68 30 (44.1%) 38 (55.9%) WEEK 4 55 37 (67.3%) 18 (32.7%) 58 46 (79.3%) 12 (20.7%) Column visit treatment,(“—” col1 col2 col3); Example2 Sales ƒƒƒƒƒƒƒƒƒ Sector min ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ ne 86 nw 30 se 40 sw 40 procreport data=grocery nowd headline; column sector sales,("--" min); define sector/group; define sales/analysis sum; run; if the first and last characters of a heading are one of the following characters, then PROC REPORT uses that character to expand the heading to fill the space over the column or columns. Note that the <> and the >< must be paired. − = . _ * + <> >< Proc Report - HASUG by Santosh Bari

12. DEFINE StatementDEFINE report-item / <option(s)>; Describes how to use and display a report item. • Tip: If you do not use a DEFINE statement, then PROC REPORT uses default characteristics. define sector / group format=$sctrfmt. "Sector"; define sales / analysis sum format=comma10.2 "Sales"; define eventspd /GROUP "Event@Stop@Day" "__" WIDTH=8 left flow spacing=1; Usage of Variables in a Report Much of a report’s layout is determined by the usages that you specify for variables in the DEFINE statements or DEFINITION windows. For data set variables, these usages are DISPLAY ORDER ACROSS GROUP ANALYSIS A report can contain variables that are not in the input data set. These variables must have a usage of COMPUTED. Proc Report - HASUG by Santosh Bari

13. Display Variables Display variables do not affect the order of the rows in the report. By default, PROC REPORT treats all character variables as display variables. Order Variables You can change the default order with ORDER= and DESCENDING in the DEFINE statement or with the DEFINITION window. If the report contains multiple order variables, then PROC REPORT establishes the order of the detail rows by sorting these variables from left to right in the report. PROC REPORT does not repeat the value of an order variable from one row to the next if the value does not change, unless an order variable to its left changes values. Example: • define manager / order order=formatted format=$mgrfmt.; • define department / order order=internal format=$deptfmt.; ORDER= specifies the sort order for a variable. This report arranges the rows according to the formatted values of Manager and the internal values of Department (np1, np2, p1, and p2). Group Variables PROC REPORT does not repeat the values of a group variable from one row to the next if the value does not change, unless a group variable to its left changes values. It is similar in procedures that use class variables, group variables are class variables. You can change the default order with ORDER= and DESCENDING in the DEFINE statement or with the DEFINITION window. DEFINE eventn /GROUP noprint; Proc Report - HASUG by Santosh Bari

14. Analysis Variables By default, PROC REPORT uses numeric variables as analysis variables that are used to calculate the Sum statistic. define sales / analysis sum format=dollar7.2 ’Sales’; define salesmin / analysis min noprint; define salesmax / analysis max noprint; Note: Be careful when you use SAS dates in reports that contain summary lines. SAS dates are numeric variables. Unless you explicitly define dates as some other type of variable, PROC REPORT summarizes them. Across Variables PROC REPORT creates a column and a column heading for each formatted value of the across variable Department. PROC REPORT orders the columns by these values. PROC REPORT also generates a column heading that spans all these columns. Quoted text in the DEFINE statement for Department customizes this heading. define department / across format=$deptfmt. ’_Department_’; Generally, you use Across variables in conjunction with order or group variables. Computed Variables Computed variables are variables that you define for the report. They are not in the input data set, and PROC REPORT does not add them to the input data set. However, computed variables are included in an output data set if you create one. Proc Report - HASUG by Santosh Bari

15. Sales Figures for Perishables in Northern Sectors _______Department_______ Sector Manager Meat/Dairy Produce Perishable Total ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ Northeast Alomar $190.00 $86.00 $276.00 Andrews $300.00 $125.00 $425.00 Northwest Brown $250.00 $73.00 $323.00 Pelfrey $205.00 $76.00 $281.00 Reveiz $600.00 $30.00 $630.00 --------------------------------------------------------- | Combined sales for meat and dairy : $1,545.00 | | Combined sales for produce : $390.00 | | | | Combined sales for all perishables: $1,935.00 | --------------------------------------------------------- Proc Report - HASUG by Santosh Bari

16. Example: Individual Store Sales as a Percent of All Sales Total Percent Sector Manager Sales of Sales ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ Northeast Alomar $786.00 12% Andrews $1,045.00 17% Sales substantially above expectations. Northwest Brown $598.00 9% Pelfrey $746.00 12% Reveiz $1,110.00 18% Sales substantially above expectations. Southeast Jones $630.00 10% Smith $350.00 6% Southwest Adams $695.00 11% Taylor $353.00 6% ƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒ $6,313.00 100% procreport data=grocery nowd headline; title; column ("Individual Store Sales as a Percent of All Sales" sector manager sales,(sum pctsum) comment); define manager / group format=$mgrfmt.; define sector / group format=$sctrfmt.; define sales / format=dollar11.2 ""; define sum / format=dollar9.2 "Total Sales"; define pctsum / "Percent of Sales" format=percent6. width=8; define comment / computed width=20 "" flow; compute comment / char length=40; if sales.pctsum gt .15 and _break_ = " " then comment="Sales substantially above expectations."; else comment=" "; endcomp; rbreak after / ol summarize; run; Proc Report - HASUG by Santosh Bari

17. Define options DEFINE report-item / <usage> <attribute(s)> <option(s)> <justification> <COLOR=color> <’column-header-1’ <…’column-header-n’>> <style>; Customize the appearance of a report item FORMAT= Assign a SAS or user-defined format to the item MISSING Consider missing values as valid values for the item ORDER= Order the values of a group, order, or across variable according to the specified order SPACING= For traditional SAS monospace output, define the number of blank characters to leave between the column being defined and the column immediately to its left (default=2) WIDTH= Define the width of the column in which PROC REPORT displays the report item Specify options for a report item CONTENTS= Create a link in the Table of Contents DESCENDING Reverse the order in which PROC REPORT displays rows or values of a group, order, or across variable FLOW Wrap the value of a character variable in its column ID Specify that the item that you are defining is an ID variable NOPRINT Suppress the display of the report item NOZERO Suppress the display of the report item if its values are all zero or missing PAGE Insert a page break just before printing the first column containing values of the report item Control the placement of values and column headings CENTER / LEFT / RIGHT Define the column heading for the report item column-heading Proc Report - HASUG by Santosh Bari

18. Define options ORDER=DATA|FORMATTED|FREQ|INTERNAL orders the values of a group, order, or across variable according to the specified order, where DATA orders values according to their order in the input data set. FORMATTED orders values by their formatted (external) values. If no format has been assigned to a class variable, then the default format, BEST12., is used. FREQ orders values by ascending frequency count. INTERNAL orders values by their unformatted values, which yields the same order that PROC SORT would yield. This order is operating environment-dependent. This sort sequence is particularly useful for displaying dates chronologically. Default: FORMATTED define manager / order order=formatted format=$mgrfmt.; Proc Report - HASUG by Santosh Bari

19. COMPUTE Statement COMPUTE location <target> </ STYLE=<style-element-name> <[style-attribute-specification(s)]>>; LINE specification(s); . . . select SAS language elements . . . ENDCOMP; COMPUTE report-item </ type-specification>; CALL DEFINE (column-id, ’attribute-name’, value); . . . select SAS language elements . . . ENDCOMP; Location determines where the compute block executes in relation to target. BEFORE / AFTER Proc Report - HASUG by Santosh Bari

20. Example: Report for Northeast and Northwest Sectors 1 Sector Department Sales Profit N ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ Northeast Canned $840.00 $336.00 2 Meat/Dairy $490.00 $122.50 2 Paper $290.00 $116.00 2 Produce $211.00 $52.75 2 Northwest Canned $1,070.00 $428.00 3 Meat/Dairy $1,055.00 $263.75 3 Paper $150.00 $60.00 3 Produce $179.00 $44.75 3 ========= ========= ========= ========= TOTALS: $4,285.00 $1,423.75 20 ========= ========= ========= ========= Proc Report - HASUG by Santosh Bari

21. procreport data=grocery headline headskip nowd; column sector department sales Profit N ; define sector / group format=$sctrfmt.; define department / group format=$deptfmt.; define sales / analysis sum format=dollar9.2; define profit / computed format=dollar9.2; compute before; totprof = 0; endcomp; compute profit; if sector ne " " or department ne " " then do; if department="np1" or department="np2“ then profit=0.4*sales.sum; else profit=0.25*sales.sum; totprof = totprof + profit; end; else profit = totprof; endcomp; rbreak after / dol dul summarize; compute after; sector="TOTALS:"; endcomp; where sector contains "n"; title "Report for Northeast and Northwest Sectors"; run; Proc Report - HASUG by Santosh Bari

22. STYLE<(location(s))>=<style-element-name><[style-attribute-specification(s)]>STYLE<(location(s))>=<style-element-name><[style-attribute-specification(s)]> This option affects only the HTML, RTF, and Printer destinations. target controls when the compute block executes. If you specify a location (BEFORE or AFTER) for the COMPUTE statement, then you can also specify target, which can be one of the following: break-variable - is a group or order variable. ENDCOMP Statement Marks the end of one or more programming statements that PROC REPORT executes as it builds the report. LINE Statement Provides a subset of the features of the PUT statement for writing customized summaries. • This statement is valid only in a compute block that is associated with a location in the report. Example: compute after; line ’Total sales for these stores were: ’ sales.sum dollar9.2; endcomp; Proc Report - HASUG by Santosh Bari

23. RBREAK Statement RBREAK location </ option(s)>; Produces a default summary at the beginning or end of a report or at the beginning or end of each BY group. rbreak after / dol dul summarize; Location: BEFORE, AFTER Options: DOL, DUL, UL, OL, PAGE, SKIP (Blank line at the break), SUMMARIZE (include summary line as of the break line), UL Order of Break Lines When a default summary contains more than one break line, the order in which the break lines appear is 1 overlining or double overlining (OL or DOL, traditional SAS monospace output only) 2 summary line (SUMMARIZE) 3 underlining or double underlining (UL or DUL, traditional SAS monospace output only) 4 skipped line (SKIP, traditional SAS monospace output only) 5 page break (PAGE). WEIGHT variable; Specifies weights for analysis variables in the statistical calculations. Weight values 0 (counts the observation in the total number of observations), less than 0 (converts the value to zero and counts the observation in the total number of observations), missing (excludes the observation) To exclude observations that contain negative and zero weights from the analysis, use EXCLNPWGT. Proc Report - HASUG by Santosh Bari

24. Specifying Style Elements for ODS Output [html/rtf/pdf] ods pdf file="C:\Documents and Settings\sabari\Desktop\Papers\HASUG\ods_1.pdf"; procreport data=grocery nowd headline headskip style(report)=[cellspacing=5 borderwidth=10 bordercolor=blue] style(header)=[color=yellow fontstyle=italic fontsize=6] style(column)=[color=moderate brown fontfamily=helvetica fontsize=4] style(lines)=[color=white backgroundcolor=black fontstyle=italic fontweight=bold fontsize=5] style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9 fontfamily=helvetica fontsize=3 textalign=r]; column manager department sales; define manager / order order=formatted format=$mgrfmt. "Manager"; define department / order order=internal format=$deptfmt. "Department"; break after manager / summarize; compute after manager; line "Subtotal for " manager $mgrfmt. "is " sales.sum dollar7.2 "."; endcomp; compute after; line "Total for all departments is: " sales.sum dollar7.2 "."; endcomp; where sector="se"; title "Sales for the Southeast Sector"; run; ods pdf close; Proc Report - HASUG by Santosh Bari

25. Example: Panel option Sales for the Western Sectors Manager Department Sales Manager Department Sales ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Adams Paper $40.00 Canned $225.00 Reveiz Paper $60.00 Meat/Dairy $350.00 Canned $420.00 Produce $80.00 Meat/Dairy $600.00 Produce $30.00 Brown Paper $45.00 Canned $230.00 Taylor Paper $53.00 Meat/Dairy $250.00 Canned $120.00 Produce $73.00 Meat/Dairy $130.00 Produce $50.00 Pelfrey Paper $45.00 Canned $420.00 Meat/Dairy $205.00 Produce $76.00 procreport data=grocery nowd headline formchar(2)="~" panels=99 pspace=6 ls=64 ps=18; column manager department sales; define manager / order order=formatted format=$mgrfmt. "Manager"; define department / order order=internal format=$deptfmt. "Department"; define sales / format=dollar7.2; break after manager / skip; where sector="nw" or sector="sw"; title "Sales for the Western Sectors"; run; Proc Report - HASUG by Santosh Bari

26. PANELS= creates a multipanel report. Specifying PANELS=99 ensures that PROC REPORT fits as many panels as possible on one page. PSPACE=6 places six spaces between panels. Proc Report - HASUG by Santosh Bari

27. Q & A • Funny Video Proc Report - HASUG by Santosh Bari