1 / 43

Intro to Proc Report with Excel Output

Intro to Proc Report with Excel Output. Barbara B. Okerson WellPoint West Region Client Reporting & Analytics. Presentation Outline. What is Proc Report? Syntax Customization Writing to Excel Example with healthcare data. Proc Report. SAS tool to summarize and display data

fleur
Download Presentation

Intro to Proc Report with Excel Output

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Intro to Proc Report with Excel Output Barbara B. Okerson WellPoint West Region Client Reporting & Analytics

  2. Presentation Outline • What is Proc Report? • Syntax • Customization • Writing to Excel • Example with healthcare data

  3. Proc Report • SAS tool to summarize and display data • Flexible and customizable • Use Proc Report for: • Summary Reports • Statistics • Detail Reports • Compute Blocks | FOR INTERNAL USE ONLY | DO NOT COPY

  4. Proc Report Syntax • PROC REPORT DATA= datasetname <options>; • COLUMN variable list and column specifications; • DEFINE column / column usage and attributes; • COMPUTE column; compute block statements; ENDCOMP; • RUN; | FOR INTERNAL USE ONLY | DO NOT COPY

  5. Using the COLUMN Statement • Column statement can be used to: • Identify fields of interest • Add headers • Group fields • Attach statistics to fields Fields must be listed in the order to be displayed | FOR INTERNAL USE ONLY | DO NOT COPY

  6. Using the DEFINE Statement • Lists column and attributes (attributes after / ) • The type attribute: • DISPLAY - displays the value of the field (default for character fields). • GROUP - Consolidate observations using this field. • ANALYSIS - Used in calculations with a statistic (default for numeric fields). • COMPUTED- Specifies a field that is to be created in a compute block. • ORDER - Sorts the data and forms groups for summary statistics, • ACROSS - Used to create groups across rather than down the page. | FOR INTERNAL USE ONLY | DO NOT COPY

  7. Using the DEFINE Statement (continued) • Other DEFINE statement attributes: • Format – specifies how column is formatted • Width – width of column • Noprint – do not display this column • Flow – wrap wide text • Statistic – statistic to be calculated • Style – use style elements with the report | FOR INTERNAL USE ONLY | DO NOT COPY

  8. Sample Report Syntax • title1 'Using Proc Report'; • title2 'Sample Report'; • proc report data=sample nowd; columns month group med_membersrx_members; define month/"Report Month"; define group / "Group Number"; define med_members / "Medical Members"; define rx_members / "Pharmacy Members"; • run; | FOR INTERNAL USE ONLY | DO NOT COPY

  9. Output | FOR INTERNAL USE ONLY | DO NOT COPY

  10. Report Code with Formats and HTML Output • ods html style=statistical; • title1 'Using Proc Report'; • title2 'Sample Report'; • proc report data=sample nowd; columns month group med_members rx_members; define month/"Report Month“ f=YYMMn6.; define group / "Group Number"; define med_members / "Medical Members“ f=comma7.; define rx_members / "Pharmacy Members“ f=comma7.; • run; • ods html close; | FOR INTERNAL USE ONLY | DO NOT COPY

  11. Sample Report Output with Formatting | FOR INTERNAL USE ONLY | DO NOT COPY

  12. Using the COMPUTE Statement • The COMPUTE statement can be used to: • modify values within a column • insert lines of text into the report • to create columns • control the content of a column | FOR INTERNAL USE ONLY | DO NOT COPY

  13. Add a COMPUTE to Sample Report • proc report data=sample nowd; columns month group med_membersrx_members ratio ; define month/"Report Month"; define group / "Group Number"; define med_members / analysis mean "Medical Members"; define rx_members / analysis mean "Pharmacy Members"; define ratio/computed; • compute ratio; • ratio = rx_members.mean/med_members.mean; • endcompute; • run; | FOR INTERNAL USE ONLY | DO NOT COPY

  14. New Sample Report | FOR INTERNAL USE ONLY | DO NOT COPY

  15. Creating Subtotals and Totals in Proc Report • odshtmlstyle=seaside; • title1'Using Proc Report'; • title2'Sample Report'; • procReportdata=sample nowd; • columns group month med_members • rx_members ; • define group / group"Group Number"; • define month/ order"Report Month" f=yymmn6.; • definemed_members / "Medical Members" f=comma6.; • definerx_members / "Pharmacy Members" f=comma6.; • break after group/summarize; • rbreak after/summarize; • run; • odshtmlclose; | FOR INTERNAL USE ONLY | DO NOT COPY

  16. Report With Subtotals and Totals | FOR INTERNAL USE ONLY | DO NOT COPY

  17. Group and Order • Order fields • ORDER=FORMATTED Sorts by a field’s formatted values • ORDER=DATA Sorts in the order that the field values are encountered in the data set • ORDER=INTERNAL Sorts by a field’s unformatted values • ORDER=FREQ Sorts by frequency counts of the field values • Group fields – consolidate observations under grouped fields | FOR INTERNAL USE ONLY | DO NOT COPY

  18. Additional Customizations • Titles and Footnotes • Writing to Microsoft Excel • Splitting and spanning column headers • Number formats • Modifying cell widths • Run date • Line color • Readability option | FOR INTERNAL USE ONLY | DO NOT COPY

  19. | FOR INTERNAL USE ONLY | DO NOT COPY

  20. Proc Report Code Statements proc report nowd data=sample2 split='/'; columns subgroup month ('-Medical Membership-' contracts members) ('-Total Paid-‘ ('-Medical-' inpatient_paidoutpatient_paidprofessional_paidtot_med ) pharmacy dental vision total); define subgroup / order order=data display format =$10. style=[cellwidth= .85 in]; define month / order display 'Paid Month' format=$6.; define contracts/ display '# of Subscribers' style=[tagattr="format:#,###" cellwidth= .9 in]; define members / display '# of Members' style=[tagattr="format:#,###"]; define inpatient _paid/ display 'Inpatient' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'}; define outpatient_paid / display 'Outpatient' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;' cellwidth= 1 in}; define professional _paid/ display 'Professional' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;' cellwidth= 1.1 in}; define tot_med/ display 'Medical/Paid/Amount' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'}; define pharmacy/ display 'Pharmacy' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'}; define dental / display 'Dental' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'}; define vision / display 'Vision' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'}; define total / display 'Total' style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'}; run; | FOR INTERNAL USE ONLY | DO NOT COPY

  21. Spanning Headers • Columns subgroup month • ('-Medical Membership-' contracts members) • ('-Total Paid-‘ ('-Medical-' inpatient_paidoutpatient_paidprofessional_paidtot_med) pharmacy dental vision total); | FOR INTERNAL USE ONLY | DO NOT COPY

  22. Style Attributes • define contracts/ display '# of Subscribers' style=[tagattr="format:#,###" cellwidth= .9 in]; • define members / display '# of Members‘ style=[tagattr="format:#,###"]; • define inpatient _paid/ display 'Inpatient‘ style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;'}; • define outpatient_paid / display 'Outpatient‘ style(column)={tagattr='format:$#,##0.00;[Red]($#,##0.00);[Black]0.00;' cellwidth= 1 in}; | FOR INTERNAL USE ONLY | DO NOT COPY

  23. Peripheral Code odsTagsets.ExcelXP file='R:\BOkerson\WellPoint SAS Users Group\Proc Report March 2013\new_style3.xml' style=minimal options(embedded_titles='yes' embedded_footnotes='yes'); odstagsets.excelxp options(sheet_name = 'Sample'); title1 j=c 'CXXXXX Sample Company'; title2 j=c 'CONTRACTS, MEMBERSHIP AND PAID AMOUNT BY SETTING BY MONTH'; title3 j=c 'Paid Period: Nov 01, 2012 through Dec 31, 2012'; title4 j=c 'RIM KKKKK'; Proc report…… footnote2 j=c "This confidential information should not be distributed without Anthem's prior written consent and should only be used to review health care utilization."; footnote3 j=c "The Anthem Blue Cross and Blue Shield Companies are independent licensees of the Blue Cross and Blue Shield Association."; footnote4 j=c "Group Number(s) - CXXXXX selected segments"; footnote5 j=l "Run Date = &rundate"; ODS Tagsets.ExcelXP close; | FOR INTERNAL USE ONLY | DO NOT COPY

  24. Titles and Footnotes • Justification (j=c) • Other options • Height • Font • Color • Background color • Underline | FOR INTERNAL USE ONLY | DO NOT COPY

  25. Writing to Excel • ODS tagsets.excelxp file='R:\bokerson\Sample\CXXXXX_SampleCompany_MonRpt_EDW_RIMKKKKK_201211_201212.xml‘ style=minimal options(embedded_titles='yes‘ embedded_footnotes='yes'); • ODS tagsets.excelxp options(sheet_name = ‘Sample'); • …….report statements…… • ODS tagsets.excelxp close; | FOR INTERNAL USE ONLY | DO NOT COPY

  26. Setting the Style and Destination Type • ODS tagsets.excelxp …. style=minimal …. • ODS style – collection of elements • ODS style attributes –e.g. font, color, width • ODS Tagsets – associate template with destination • ODS ExcelXPTagset Destination – output in Microsoft’s SpreadsheetML XML format • Combine style and tagset | FOR INTERNAL USE ONLY | DO NOT COPY

  27. Style=seaside • Bolded title and headers • Shaded background for title cells • Colored grid lines | FOR INTERNAL USE ONLY | DO NOT COPY

  28. Style=default • Shaded backgrounds • Italic and bolded header • White grid lines | FOR INTERNAL USE ONLY | DO NOT COPY

  29. Available SAS Styles • To view available styles run the following code: • proc template; • list styles; • run; • SAS writes the list of available styles in the output window. | FOR INTERNAL USE ONLY | DO NOT COPY

  30. Add Group And Order • Order: • define subgroup / orderorder=data display format =$10. style=[cellwidth= .85 in]; • define month / order display 'Paid Month' format=$6.; • Group: • define subgroup / group order=data display format =$10. style=[cellwidth= .85 in]; • define month / order display 'Paid Month' format=$6.; | FOR INTERNAL USE ONLY | DO NOT COPY

  31. Order and Group Output • Corner of original report. • Corner of report with subgroup as group field. | FOR INTERNAL USE ONLY | DO NOT COPY

  32. Automatic Run Date • Store date in macro field: data _null_; today_date=today(); CALL SYMPUT('rundate',put(today_date,mmddyy10.)); run; • Use macro field in footnote: footnote5 j=l "Run Date = &rundate"; | FOR INTERNAL USE ONLY | DO NOT COPY

  33. Other Enhancements • Line color • Highlight every other row • Write to multiple worksheets | FOR INTERNAL USE ONLY | DO NOT COPY

  34. Proc Template to Modify Line Color • proc template; • define style styles.test; • parent=styles.minimal; • style table from table/ • bordercolor =blue; • end; • run; • odsTagsets.ExcelXP file='R:\BOkerson\WellPoint SAS Users Group\new_style4x.xml' style=test options(embedded_titles='yes' embedded_footnotes='yes' ); | FOR INTERNAL USE ONLY | DO NOT COPY

  35. | FOR INTERNAL USE ONLY | DO NOT COPY

  36. Compute Group Code for Line Shading • Compute subgroup; • line_count+1; • if mod(line_count,2)=0 then do; • call define (_row_, "style", • "style=[background=cxF2F2DF]"); • end; • Endcomp; | FOR INTERNAL USE ONLY | DO NOT COPY

  37. | FOR INTERNAL USE ONLY | DO NOT COPY

  38. Code for Multiple Sheets with By-Value • odsTagsets.excelxp • file='R:\BOkerson\totals2.xml' style=minimal options(embedded_titles='yes' embedded_footnotes='yes' ); • odstagsets.excelxpoptions(sheet_name = '#byval(subgroup)'); • proc Report nowd data=sample3 headline headskip split='/'; • columns ('-#byval(Subgoup)-'month ('-Medical Membership-' • contracts members) ('-Total Paid-' ('-Medical-' inpatient_paid • outpatient_paidprofessional_paidtot_med) pharmacy )); • define month / order 'Paid Month' format=$6.; • ............. • ............. • by subgroup; • run; • ODS Tagsets.excelxp close; | FOR INTERNAL USE ONLY | DO NOT COPY

  39. Proc Report By Value Output | FOR INTERNAL USE ONLY | DO NOT COPY

  40. Add a Logo to Proc Report Output • MSOffice2K_x tagset • Download from http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html • Run the code before running Proc Report • WellPoint logo in upper left corner • Options image_path="R:\bokerson\wellpoint_logo.gif" image_height="45" image_width="90" |

  41. | FOR INTERNAL USE ONLY | DO NOT COPY Questions???

  42. Contact Information • Barbara B. Okerson, Ph.D • Senior Health Information Consultant,  • West Region Client Reporting & Analytics • WellPoint Health Care Analytics • Phone: 804-662-5287 • Email: barbara.okerson@wellpoint.com | FOR INTERNAL USE ONLY | DO NOT COPY

More Related