Proc Report Tricks

Proc Report Tricks Kelley Weston

Examples • Text that spans columns • Patient-level detail in the titles • Titles and footnotes at different places • Adding a space when adding a space doesn't work • Using a character that needs to sort before a space • Using a better underline • Stacking multiple variables in one column • Avoid orphaning groups of rows in listings Ex 1Ex 2Ex 3Ex 4Ex 5Ex 6Ex 7Ex 8

Text that spans columns • Desired Output: • Patient Date • 1 01NOV2005 • Test 1: this is my sample long text • Test 2: this is my sample long text • 2 02NOV2005 • Test 1: this is my sample long text • Test 2: this is my sample long text

Notice that: • Some information is in columns, under column headings (patient number and date) • Some information is on multiple lines that spans columns (test 1 and test 2)

Sample code: Create dataset: data work.temp; input pt : $1. date : date9. @13 mytext $char50.; cards; 1 01nov2005 this is my sample long text 2 02nov2005 this is my sample long text run;

Report code: Part 1: proc report data = work.temp nowd ; %* variable to be used in compute block must come before break variable; column mytext pt date; define pt / order "Patient" width = 7 center ; define date / order "Date" width = 9 format = date9. left ;

Report Code Part 2: *the variable to be used in the compute block must be defined as an order variable and use the noprint option; define mytext / order noprint; compute after pt; %* 1st method of printing line; line @3 "Test 1: " @12 mytext $50.; line " "; %* 2nd method of printing line; length text $75; text = "Test 2: " !! mytext; line @3 text $75.; line " "; endcomp; break after pt / skip; run;

Patient-level detail in the titles • Desired Sample Output: • ____________ • Sample report <- title 1 line • Patient: 1 Y = A <- title2 line • _________ • Z <- column heading • _________ • 9 <- variable value • _____________ • Sample report • Patient: 2 Y = B • ____________ • _________ • Z • _________ • 8 Outline

Outline: • Put patient information in macro variables • Create proc report with titles containing macro variables • Wrap proc report within a macro loop to process all patients

Create dataset: data work.temp; input patient y $ z; cards; 1 A 9 2 B 8 run;

Step 1 - Put patient information into macro variables procsql noprint; select count(patient) into :cnt_x from work.temp; %let cnt_x = &cnt_x; select distinct patient, y into :x1 - :x&cnt_x /* patient number */ , :y1 - :y&cnt_x /* other var. to be in ttl */ from work.temp; quit;

Step 2 - Create proc report with titles containing macro variables title1 "________________________________________"; title2 "Sample report"; title3 "Patient: &&x&cnt Y = &&y&cnt"; title4 "________________________________________"; proc report data = work.temp nowd headline; column ("__" z); define z / "Z"; where Patient = &&x&cnt; run;

Step 3 - Wrap proc report within a macro loop to process all patients %macro test; %local cnt; %do cnt = 1 %to &cnt_x; title1 "________________________________________"; title2 "Sample report"; title3 "Patient: &&x&cnt Y = &&y&cnt"; title4 "________________________________________"; proc report data = work.temp nowd headline; column ("__" z); define z / "Z"; where Patient = &&x&cnt; run; %end; %mend test; %test;

Titles and footnotes at different places Outline

Titles within proc report: proc report data = work.report column x; Define x / display; compute before ; line "Title at the beginning of the report"; line " "; endcomp; %* between Titles & column headers; compute before _page_ / center; line "Title at the beginning of each page"; line " "; endcomp; run;

Footnotes within proc report: proc report data = work.report column x; Define x / display; %* before footnotes specified in Footnote stmts; compute after _page_ / center; line " "; line "Footnote at the end of each page"; endcomp; compute after; line " "; line "Footnote at the end of the report"; endcomp; run;

Adding a space when adding a space doesn't work • If Proc Report does not maintain the space you need (for example, in a label), then you can use ASCII 160 / hex A0) – used in column titles to indent (Proc Report won't keep space [ASCII 32]) • Define x / " Column* Title" left; • Could still be printed as: • Column • Title • When you really want: • bbColumn • bbbbTitle

Two ways of achieving this: If using UltraEdit, use the ASCII table: View / ASCII table / click on Dec 160 / hex A0 / Click on Insert char

If you are not using UltraEdit, then use the numeric keypad: Alt – 0-1-6-0 (hold down the Alt key for the entire number.)

Using a character that needs to sort before a space If you have something in your report dataset that needs to sort before something that contains a space, insert a null character (ASCII 00 / hex 00) You can insert it into your data in the same way as inserting the "new" blank (ASCII 160). For instance, when creating Adverse Event tables, and indenting the PT (Preferred term) underneath the SOC (System Organ Class), and trying to get everything to sort properly, you can precede the SOC with an ASCII 00, which will sort before the space (ASCII 32 / hex 20)

Using a better underline (use "~~" in column statement, then use customized SAS macro)

Step 1: Run Proc Report to create *.lst file Step 2: Run macro that will replace "~" with graphics character

Step 1: Run Proc Report: proc report formchar(2) = "~"; /* by default this is a dash */ columns ("~~" vars1 - vars3 ); /* a dash is commonly used here */

Step 2: Run macro to replace "~" with graphics dash: The macro contains the following code: if index(text, '~') > 0 then text = tranwrd(text, '~', '97'x); This replaces the tilde (~) with a graphics hyphen (—) in the list file. Normally, a regular hyphen (-) will be used, which will show space between the hyphens. This assumes that the tilde will not be used anywhere else in the report.

Stacking multiple variables in one column • The split character defined in the Proc Report statement will work not only in the label, but in the data as well: • col1 = trim(left(var1)) !! "#" !! trim(left(var2)); • proc report split = "#" ;

Avoid orphaning groups of rows in listings Adapted from "Controlling Page Breaks when using Proc Report", paper by Cynthia Stetz, Merrill Lynch There are times when you may want to keep all of group of items on a single page, instead of starting the group in the middle of one page, and continuing the group on the next page. Instead of trying to account for it yourself, let SAS do it.

Step 1: Define number of observations in every group - used for page breaks in report Step 2: Increment the page counter Step 3: Proc Report Code

Step 1: Define # of obs in every group - used for page breaks in report • proc sql noprint; • create table work.report as • select *, count(visit) as rec_cnt • from work.report • group by site • order by site, visit; • quit; • Notes: • Site is the group-by, order-by, first. variable in this example. This is a variable that has the same value on many observations. • Visit is the variable that is being counted. It has a different value on every observation. It is not a group-by variable.

Sample Output Obs site VISIT rec_cnt 1 00001 2 6 <- + 2 00001 4 6 | 6 obs 3 00001 6 6 | in 4 00001 7 6 | first 5 00001 8 6 | group 6 00001 9 6 <- + 7 00002 2 7 <- + 8 00002 4 7 | 7 obs 9 00002 6 7 | in 10 00002 7 7 | second 11 00002 8 7 | group 12 00002 9 7 | 13 00002 11 7 <- +

Step 2: Increment Page Counter data work.report (drop = max lns_on_pg); set work.report; by site; retain lns_on_pg 0 max 48 /* less than page size value */ pg_ejct 1 /* break var. in proc report */; if first.site then do; %* +2 is to compensate for break lines; lns_on_pg = lns_on_pg + rec_cnt + 2; if lns_on_pg GT max then do; pg_ejct + 1; lns_on_pg = rec_cnt + 2; end; %* lns_on_pg GT max; end; %* first.site; run;

Sample Output: After defining page eject (only 1st obs of each group shown) Obs site VISIT rec_cnt pg_ejct 1 00001 2 6 1 <- + all obs ... | for these 7 00001 2 7 1 | sites 14 00003 2 8 1 | on 22 00004 2 8 1 | page one 30 00005 2 8 1 <- + 38 00006 2 6 2 page 2 starts

Step 3: Proc Report Code: proc report; columns ("__" pg_ejct var1 -- var5); define pg_ejct / order noprint; break after pg_ejct / page; run;

Thank you ! Kelley Weston Quintiles

Proc Report Tricks