SAS coding used in creating a routine report

1. SAS coding used in creating a routine report February 23, 2009 February is Heart Month

2. How it all started… • Create a monthly report that includes: • Tables with accompanying text • Graphs with accompanying text • Background introduction to the report • Table of contents • Title page

3. ODS RTF to create the output file ODS RTF file=‘pathname/filename.rtf’; {SAS statements} ODS RTF close;

4. How to…? • Format text • Insert special characters • Format headers and footers • Format a table • Insert pictures/graphs • Call in an R program from SAS Stress-busting tip for a healthy heart: Laugh. It’s your body’s natural stress-release mechanism

5. Basic formats in TITLE • TITLE and FOOTNOTE have some basic formats styles available using keywords: • Bold • Background/Foreground colour • Font sizes • Font types • Text justification • Underlining TITLEjustify=r boldcolor=blue bcolor=red font=arial height=10 underlin=1 ‘put text here!’;

6. Format keywords in PROC REPORT • Similar to TITLE but some keywords differ PROC REPORT data=notes style(header)=[background=STG foreground=white just=center font_size=9pt font_face='arial' font_weight=bold]; column text; define text / style={foreground=black background=yellow cellwidth=10 cm font_size=11pt font_face='arial'}; run;

7. ODS ESCAPECHAR • Define a special character to indicate RTF elements • Choose a character that is not commonly used in SAS coding ODS ESCAPECHAR = ‘^’; {SAS coding} DATA notes; length text $32767; text="^R'{\b\iPut text here! }’^R'super\1’ "; run;

8. RTF control words • Control word – specially formatted command that RTF uses to mark printer control codes and information

9. RTF control symbols and groups • Control symbol – backslash followed by a single, non-alphabetic character • Example: \~ for non-breaking space • Groups – text and control words or control symbols between braces, {}

10. Specify style elements • Specify ODS ESCAPECHAR • Basic form: ^S={style attributes} ^S={indent=0.45cm cellwidth=20cm font_face='arial' font_size=11pt}

11. Adding text into RTF file • TITLE adds text to top of page • FOOTNOTE adds text to bottom of page • How about adding text to the middle of the page or blocks of text? • ODS RTF TEXT • PROC REPORT

12. ODS RTF TEXT • Add text before/after a PROCedure ODS RTF TEXT = “Add text here"; • Add formatting using STYLE: ODS RTF TEXT = "^S={} {Add in text here.}"; ODS RTF TEXT = "^S={indent=0.45cm cellwidth=20cm font_face='arial' font_size=11pt}{Add in text here.}";

13. Adding text using PROC REPORT • For larger blocks of text PROC REPORT • Basic PROC REPORT PROC REPORT data=notes; column text; define text / display; run; ODS ESCAPECHAR=‘^’; ODS RTF file=‘pathname/sample.rtf’; PROC REPORT data=notes noheader center nowd; column text; define text / width=96 flow style={foreground=black background=white cellwidth=17.75cm font_size=11pt font_face='arial'}; run; ODS RTF close;

14. Creating text in a DATA step DATA notes; length text $32767; text=“Insert text here”; run; DATA notes; length text $32767; text= ^S={font_weight=bold font_size=14pt foreground=STG}^R'\qc' Insert centered bolded text here and add a new line ^n ^S={}"|| "^R'\par\ql' Begin a left justified paragraph. "|| “Add in more text and then add 2 lines. ^n^n”; run;

15. How to…? • Format text • Insert special characters • Format headers and footers • Format a table • Insert pictures/graphs • Call in an R program from SAS Healthy-eating for a healthy heart. Choose fresh fruit over juice.

16. Special Characters From START menu: Programs > Accessories > System Tools > Character Map Copy/Paste symbol into SAS editor If symbol pastes as ‘?’ then it will not display properly

17. A truffle a day lowers blood pressure. Choose chocolate with cocoa content of 70% or higher. How to…? • Format text • Insert special characters • Format headers and footers • Format a table • Insert pictures/graphs • Call in an R program from SAS

18. Headers & Footers • Format text in TITLE and FOOTNOTE using keywords (ie. bold, bcolor=, underlin=, font=) or in-line formatting • Insert pictures using PREIMAGE • JPG, GIF and PNG • Create sections by using the justification format code • just=center(c), left(l), right(r)

19. Headers & Footers FOOTNOTE j=r "^S={font=(‘arial’, 12pt, bold) Insert bold text here.}"; TITLE j=l "^S={preimage='W:\Reports\image1.png'}" j=r "^S={preimage='W:\Report\image2.png'}";

20. How to…? • Format text • Insert special characters • Format headers and footers • Format a table • Insert pictures/graphs • Call in an R program from SAS Stress-busting tip for a healthy heart. Take time for yourself.

21. Format a table – PROC REPORT • Format rows & columns • Add subscripts to text • Highlight cells based on a conditional statement

22. Data setup for formatted table • PROC TABULATE, PROC TRANSPOSE and DATA step to arrange data • Merge with dataset containing: • Variable labels (varlabel) to create formatted text in table (note: RTF control words to format) • Categories for grouping variables • Variable (npage) to identify where to split table

23. PROC REPORT table PROC REPORT data=dproc2 nowd; column npage category varlabel maxrate ("Hospital Completion %" SiteA SiteB SiteC SiteD SiteE); define npage/ group noprint; define category/ group noprint order=data; define varlabel / display ‘Data Field' center; define maxrate … run;

24. Adding formats to PROC REPORT PROCREPORT data=dproc2 nowd center style(header)=[background=STG foreground=white just=center font_size=9pt font_face='arial' font_weight=bold] style(lines)=[font_size=9pt font_face='arial' just=l font_weight=bold indent=0.2 cm] style(column)=[font_size=9pt font_face='arial' cellwidth=2 cm]; …

25. Formatting specific columns PROC REPORT data=sample_data nowd center … define varlabel/ display order=data 'Data Field' left style(column)=[cellwidth=7.3 cm indent=0.9 cm]; define maxrate/ display 'Highest Completion %' center style(column)=[foreground=STG font_weight=bold cellwidth=2.7 cm]; run;

26. Conditional formatting PROC REPORT data=dproc2 nowd center … compute varlabel; if varlabel='Number of Cases' then call define(_ROW_, "style","style=[background =PALG font_weight=bold font_style=italic]"); endcomp; %traffic_lighting (SiteA SiteB SiteC SiteD SiteE maxrate, 6); break after npage/ page; run;

27. Traffic Lighting %macro traffic_lighting (var, nvar); %do i=1 %to &nvar; %let rvar=%scan(&var,&i); compute &rvar; if 0<= &rvar <50 then call define (_COL_, "STYLE", "style=[background=dark red foreground=white font_weight=bold]"); if 50 <= &rvar < 90 then call define(_COL_, "STYLE", "STYLE=[background=gold foreground=black font_weight=bold]"); endcomp; %end; %mend traffic_lighting;

28. How to…? • Format text • Insert special characters • Format headers and footers • Format a table • Insert pictures/graphs • Call in an R program from SAS Nuts and seeds are high in heart healthy fat. Be sure to reduce other added fats if including nuts and seeds in your diet

29. Adding in a picture in ODS RTF DATA cover_page; list='^S={preimage="W:\Reports\cover_image.png"}'; PROCREPORT data=cover_page noheader nowd style={frame=void}; define list /style={cellwidth=21 cm} center; run;

30. How to…? • Format text • Insert special characters • Format headers and footers • Format a table • Insert pictures/graphs • Call in an R program from SAS Keep active and maintain a healthy weight. Taking the stairs burns 5x as many calories as taking the elevator.

31. Call an R program from SAS OPTIONSXWAIT XSYNC; DATA _null_; X """C:\Program Files\R\R.exe"" --no-save --quiet < ""W:\graphs2.R"" > ""W:\graphs.log"""; infile 'W:\graphs.log'; file log; input; put '**R: ' _INFILE_; run;

32. Creating the output file ODS ESCAPECHAR='^'; ODS RTF file=‘filename‘ startpage=no; {Create cover page} ods rtf startpage=now; {Insert background text} … ODS RTF close;

33. THANKS!