Get Fancy Department Level

1
Get Fancy Department Level Reports Using SAS
ExcelXP Tagset 2008 AIR Forum Seattle,
Washington Ray Wallace (rwallace_at_wsu.edu) Washing
ton State University View Presentation at
http//www.ir.wsu.edu/
2

• Presentation Overview
• SAS Tagsets (Excel features from SAS)
• Style (Get it to look right)
• Department Looping (Macros)

3

• What is a Tagset?
• Adds tags to output using ODS
• Ex. HTML tag ltbodygtPut Body of Web Page
  Herelt/bodygt
• ExcelXP tagset uses xml tags, Ex.
  ltRowgtltCellgtltDatagt data in the celllt/Datagtlt/Cellgtlt/
  Rowgt
• Developed by Eric Gebhart from SAS
• Get the latest tagset code
• http//support.sas.com/rnd/base/ods/odsmarkup/inde
  x.html
• New version 1.86 available as of April 2008

4

• Install the Latest Tagset
• Download the latest version from support.sas.com
• Save the .tpl file
• Open the .tpl file in SAS
• Run it
• ExcelXP Tagset Code

5

• How Does A Tagset Work?
• A Tagset is just another ODS destination.
• E.g.,
• listing
• html
• pdf
• Tagsets (such as ExcelXP)

6

• How Does A Tagset Work?
• Things you can do to an ODS Destination
• Open ? ods tagsets.ExcelXP filefoo.xls
• Manage ? ods tagsets.ExcelXP
  options(embedded_titlesyes)
• Close ? ods tagsets.ExcelXP close

7

• Get A List Of The Available Tagsets
• proc template
• list tagsets
• run

User-defined tagsets will be located in
SASUSER.TEMPLAT. Tagsets provided by SAS are in
SASHELP.TMPLMST
8

• How Does Style Work?
• Works with the ODS
• Define with proc template
• When issuing an ODS command style is implied
• Ex
• ods tagsets.excelxp file foo.xls is the same
  as
• ods tagsets.excelxp file foo.xls style
  default

9

• Get A List Of The Styles You Have
• proc template
• list styles
• run
• Just like tagsets
• User-defined styles will be in SASUSER.TEMPLAT
• Styles provided by SAS are in SASHELP.TMPLMST

10

• Make Your Own Style
• Find a style you like then tweek it a little.
• proc template
• source styles.default
• run

11
Easy To Create Your Own Style Use inheritance
then add your own twists proc template define
style irstyle_xl parent styles.default
style systemtitle from systemtitle /
backgroundwhite foregroundcx990033
end
12

• Building a Report
• Department Level Accreditation Data
• Want a consolidated and concise report of
  information from various topic areas
• Display multiple years of data
• Easy for users to read
• Familiar format (Excel)
• Metadata/data definitions
• Extensible (able to add reports easily)
• Printable

13

• ExcelXP Options
• Over 60 options and controls allow SAS to control
  Excel.
• Including
• Table of Contents/Index
• Customizing column widths
• Zooming
• Header / footer control
• Excel comments
• Repeat cells
• Sheet naming
• Page orientation

Get Documentation ods Tagsets.ExcelXP file
"c\test.xls options (doc'help') ods
tagsets.Excelxp close
14

• ExcelXP Options
• Determine the zoom level on the worksheet
• ods tagsets.excelxp
• options (zoom88)
• 100 is the default

15

• ExcelXP Options
• Repeat header row on each page for printing
• ods tagsets.excelxp
• options(row_repeatheader)
• Also
• Use these options
• none -default
• 1-3
• 5

16

• ExcelXP Options
• Create a sheet which has links names of all other
  worksheets.
• ods tagsets.excelxp
• options(indexyes)

17

• ExcelXP Options
• Name the worksheets
• ods tagsets.excelxp
• options (sheet_nameOverview Data)
• Could use a macro var
• dept

18

• ExcelXP Options
• When to create a new sheet
• ods tagsets.excelxp
• options (sheet_intervalproc)
• Table is the default
• Also use page, bygroup, proc, none

19

• ExcelXP Options
• Format the page for printing
• ods tagsets.excelxp
• options (orientationlandscape)

20

• ExcelXP Options
• Tells Excel the width of each column
• ods tagsets.excelxp
• options (default_Column_Width12,9,6,6,6,6,6,8
  )

21

• ExcelXP Style Options
• Use the currency format from Excel.
• In the proc report, proc print etc.
• define fy2003 / analysis
• style(column)tagattr"format,0_)Red(,
  0)"
• Others
• format0.0
• format0.00

22

• Using Style
• Using proc format with Excel comments
• First
• proc format
• value subjdesc
• MATHMathematics
• GENEDGeneral Education
• ...more subject formats...
• Then

23

• ExcelXP Style Options
• Print comments in the appropriate cells down the
  column
• define prefix / group Prefixstyle(column)flyo
  versubjdesc.
• Or explicitly define ? fyloverHello World

24

• Three Levels of Style Hierarchy
• Document level (ods statement)
• ods tagsets.ExcelXP file c\Report for
  dept..xlsstyleirstyle_xl

25

• Three Levels of Style Hierarchy
• Procedure level (proc report statement)
• proc report datasashelp.class
• style(header)font_weightbold

26

• Three Levels of Style Hierarchy
• 3a. Variable level e.g., define statement
  under proc report.
• define taught_to / group style(column)flyovero
  wnother.

27

• Three Levels of Style Hierarchy
• 3b. Variable level - break and rbreak statement
  in proc report
• rbreak after / ol summarize style(summary)Total
• Total is a style element defined in the
  irstyle_xl.
• style total from summary /
• flyover
• font_style italic

28

• Two Levels of Style Specificity
• Two levels of specificity
• Style definition reference
• style(header)hrheader
• (predefined in irstyle_xl)
• Direct
• style(column)font_size2

29

• Macro Looping
• To have your program create an Excel file for
  each academic department
• Use a macro!
• macro deptloop(dept)
• SAS CODE
• mend deptloop
• deptloop(CHEM)

30

• Macro Looping
• What goes in the SAS CODE portion of the
  program?
• First open the ODS destination and manage options
  for the first report.
• ods tagsets.ExcelXP
• file c\Report for dept..xlsstyleirstyle_xl
  
• options (sheet intervalproc
• sheet_nameFirst dept report)

31

• Macro Looping
• What goes in the SAS CODE portion of the
  program?
• Then maybe some traffic lighting
• proc format
• value traffic
• low - lt10 red
• 10 - lt50 yellow
• 50 - high green

32

• Macro Looping
• What goes in the SAS CODE portion of the
  program?
• Now, the first report using proc print.
• proc print data deptreport1
• var subject / style(column)flyoversubjdesc.
  
• var headcount / style(header)backgroundcxD9979
  5
• style(column)backgroundtraffic
  .
• where dept dept
• run

33

• Macro Looping
• What goes in the SAS CODE portion of the
  program?
• Use ExcelXP options again for the next report
• ods tagsets.ExcelXP
• options(sheet_nameSecond Dept Report
• default_Column_Width12,6,6,6,6,6)
• frozen_header2
• )

34

• Macro Looping
• What goes in the SAS CODE portion of the
  program?
• Write the second report.
• proc report data deptreport2 nowd missing
• column subject acad_year, fte
• define subject/ group Course Subject
• define acad_year / across AY
• define fte /analysis sum FTE
• style(column)tagattr"format0.0
  
• where dept dept
• run

35

• Macro Looping
• What goes in the SAS CODE portion of the
  program?
• Now finish it up.
• ods tagsets.ExcelXP close
• This closes the ODS and finishes creating the
  Excel file.
• Then the macro must end and...
• mend deptloop
• SAS must call the macro for the code to
  actually run.
• deptloop(CHEM)

36

• Macro Looping
• What if you have 100 department and dont want
  to write 100 deptloop() macro calls?
• create macro var for a percent sign
• let pctsignstr()
• This will write all your macro calls for you
• data _null_
• set dept_list end lastobs
• file "c\call the macro program.txt"
• put "pctsign" "deptloop (" dept (1-1) ")"
  
• run
• This will run all your macro calls for you
• include "c\ call the macro program.txt"

37
Question? Contact me at Ray Wallace
(rwallace_at_wsu.edu) Washington State
University View Presentation at
http//www.ir.wsu.edu/
38
Resources SAS-L newsgroup SAS emits SAS tech
support Tagset Links http//support.sas.com/events
/sasglobalforum/index.html http//support.sas.com/
rnd/base/ods/odsmarkup/ Style Links http//www.lau
renhaworth.com/publications/195-28.pdf http//www.
laurenhaworth.com/publications/132-30.pdf http//w
ww.guptaprogramming.com/images/ODS_Custom.pdf