PROC REPORT and ODS

[ScottBass]

Hi,

I have a need to print:

Page 1: data
Page 2: dynamic questions (change each month) and signature block
Page 3: more data

I'm finding it difficult to accomplish #2 using PROC REPORT. It is easier if pages 2 and 3 are switched, as I can break on a different variable (userid vs. pagevar).

I'm wondering if I need to switch to data _null_ report. However, PROC REPORT seems (???) to make it easier to create the various style settings. The output is PDF.

One quirk of the desired report is that the columns mean different things in different sections of the report. So, I have to dynamically build section titles as well.

I have downloaded and read a number of SUGI papers by Pass, Hamilton, Haworth, Gebhart, et. al. I would likely use SUGI 28, Paper 22-28, O'Connor as a guide should I switch to data _null_ for this report.

Below is my draft/test code. Apologies for the length, but it *is* totally self-contained, so you can just cut and paste it and run it as is.

At this point, I would just like architectural advice on whether PROC REPORT is the right tool here, or whether I should switch to data _null_.

Thanks,
Scott

P.S: I also wonder whether this code surfaces a SAS bug? flag=2 rows should be Lightgrey, and traffic lighting is incorrect for the flag=2 rows. Thoughts?

Test Code:

* %let path = %sysfunc(pathname(work));
%let path = C:\Temp;

proc format;
invalue page
1001-1024 = 1 /* Page 1 */
1025-1056 = 2 /* Page 2 */
;
invalue type1_
1001-1008
,1025-1044 = 1 /* PRODUCTIVITY */
1009-1024
,1045-1056 = 2 /* QUALITY */
;
invalue type2_
1001
,1009-1016
,1025-1030 = 1 /* MEMBER INTERACTIONS */
1002-1008
,1017-1021
,1031-1044
,1045-1055 = 2 /* SALES */
1022-1024
,1056 = 3 /* OTHER */
;
invalue type3_
1001
,1009-1016
,1025-1030 = 0 /* No subitems under type2 */
1002-1003
,1017-1018
,1031-1038
,1045-1052 = 1 /* Health Insurance */
1004-1008
,1019-1021
,1039-1044
,1053-1055 = 2 /* General Insurance */
1022-1024
,1056 = 3 /* Quality Assurance */
;

value title
1 = "Primary KPI's"
2 = "Secondary KPI's"
;
value type1_
1 = "PRODUCTIVITY"
2 = "QUALITY"
;
value type2_
1 = "MEMBER INTERACTIONS"
2 = "SALES"
3 = "OTHER"
;
value type3_
0 = " "
1 = "Health Insurance"
2 = "General Insurance"
3 = "Quality Assurance"
4 = "Claims/Payments"
5 = "Enquiries"
;
value label
1001 = "Inbound Calls per Hour"
1002 = "New Business Quotes per 100 Calls"
1003 = "Existing Business Quotes per 100 Calls"
1004 = "Home - New Business Quotes per 100 Calls"
1005 = "Home - Existing Business Quotes per 100 Calls"
1006 = "Motor - New Business Quotes per 100 Calls"
1007 = "Motor - Existing Business Quotes per 100 Calls"
1008 = "Travel - Quotes per 100 Calls"
1009 = "Greet & Build Rapport"
1010 = "Establish Needs"
1011 = "Recommend the Product"
1012 = "Member Needs Linked to Product"
1013 = "Handling Objections"
1014 = "Closed Sale"
1015 = "FSR/Compliance"
1016 = "AVA Opportunity Spotted and Taken"
1017 = "Conversion Rate - New Business Quotes"
1018 = "Conversion Rate - Existing Business Quotes"
1019 = "Home - Conversion Rate New Business Quotes"
1020 = "Motor - Conversion Rate New Business Quotes"
1021 = "Travel - Conversion Rate"
1022 = "GI QA Monitoring"
1023 = "GI QA Paperwork"
1024 = "HI QA"

1025 = "Outbound Calls per Hour"
1026 = "Product Specialist Calls/Inbound Calls (%)"
1027 = "Inside Line Calls/Inbound Calls (%)"
1028 = "Average Handling Time"
1029 = "Wrap"
1030 = "Idle"
1031 = "Hospital - New Business Quotes per 100 calls"
1032 = "Hospital - Existing Business Quotes per 100 calls"
1033 = "Essentials - New Business Quotes per 100 calls"
1034 = "Essentials - Existing Business Quotes per 100 calls"
1035 = "Twin Pack - New Business Quotes per 100 calls"
1036 = "Twin Pack - Existing Business Quotes per 100 calls"
1037 = "Gap Saver - New Business Quotes per 100 calls"
1038 = "Gap Saver - Existing Business Quotes per 100 calls"
1039 = "Building - New Business Quotes per 100 calls"
1040 = "Building - Existing Business Quotes per 100 calls"
1041 = "Contents - New Business Quotes per 100 calls"
1042 = "Contents - Existing Business Quotes per 100 calls"
1043 = "Personal Valuables - New Business Quotes per 100 calls"
1044 = "Personal Valuables - Existing Business Quotes per 100 calls"
1045 = "Hospital - Conversion Rate New Business Quotes"
1046 = "Hospital - Conversion Rate Existing Business Quotes"
1047 = "Essentials - Conversion Rate New Business Quotes"
1048 = "Essentials - Conversion Rate Existing Business Quotes"
1049 = "Twin Pack - Conversion Rate New Business Quotes"
1050 = "Twin Pack - Conversion Rate Existing Business Quotes"
1051 = "Gap Saver - Conversion Rate New Business Quotes"
1052 = "Gap Saver - Conversion Rate Existing Business Quotes"
1053 = "Building - Conversion Rate New Business Quotes"
1054 = "Contents - Conversion Rate New Business Quotes"
1055 = "Personal Valuables - Conversion Rate New Business Quotes"
1056 = "Data Scripting Recording"
;

value colhead
.A = "This Month"
.B = "Bench~-2nmark"
.C = "Team Average"
.D = "MSC Average"
.E = "Last Month"
.F = "Last Quarter"
.G = "YTD Average"
.H = "Rating"

.I = "Number of Obser-~-2nvations"
.J = "Average Score This Month"
.K = "Average Score Last Month"

.N = "N/A"

.Z = " "
;
run;

* test data ;
data msc;
length userid $3 skill team $20 line page type1 type2 type3 col1-col8 8;
array vars userid skill team;
array cols col1-col8;
do line=1001 to 1056;
do over cols;
cols = line-1000;
end;
page = input(put(line,best.),page.);
type1 = input(put(line,best.),type1_.);
type2 = input(put(line,best.),type2_.);
type3 = input(put(line,best.),type3_.);
output;
end;
run;

* footer data ;
data behaviours;
length btype1_ btype2_ $50 label $120;
infile datalines dlm="|";
input btype1_ btype2_ label;
datalines;
BEHAVIOURS | MAINTAINING OPTIMISM | What have I done/am I doing to maintain a positive outlook towards others? Provide examples.
BEHAVIOURS | MAINTAINING OPTIMISM | What have I done/am I doing to maintain an optimistic outlook towards task outcomes? Provide examples.
BEHAVIOURS | EMBRACING CHANGE | What have I done to proactively and openly embrace change. Provide examples.
BEHAVIOURS | EMBRACING CHANGE | What have I done and said to others that reinforces the positive benefits of change. Provide examples.
;
run;

* massage test data ;
data msc;
set msc (in=a) msc (in=b);
if a then do;
userid = "ABC";
skill = "Reading";
team = "Perth";
end;
else
if b then do;
userid = "DEF";
skill = "Writing";
team = "Sydney";
end;
run;

proc sort data=msc;
by userid page type1--type3;
run;

* create macro variables from footer data ;
data _null_;
set behaviours end=eof;
array vars{*} _character_;
do i=1 to dim(vars);
call symput(vname(vars{i})||put(_n_,best.-L),trim(left(vars{i})));
end;
if (eof) then
call symput("num",trim(put(_n_,best.-L)));
run;
%put &btype1_1 &btype2_1 &label1;
%put &btype1_2 &btype2_2 &label2;
%put &btype1_3 &btype2_3 &label3;
%put &btype1_4 &btype2_4 &label4;
%put #

%macro rename(prefix,num);
%do i=1 %to #
&prefix&i = _&prefix&i
%end;
%mend;

* massage test data to get in the desired format for proc report ;
data report;
length userid $3 skill team $20 page type1-type3 flag 8 label $120 col1 - col8 8;
format col1-col8 colhead.;
set msc (rename=(%rename(col,8)));
by userid page type1--type3;

array cols{*} col1--col8;
array _cols{*} _col1--_col8;
array head1{8} _temporary_ (.Z .Z .Z .Z .Z .Z .Z .Z);
array head2{8} _temporary_ (.A .B .C .D .E .F .G .H);
array head3{8} _temporary_ (.I .J .C .D .K .F .G .H);
array head4{8} _temporary_ (.A .B .C .D .K .F .G .H);

if first.type1 then do;
flag = 1;
label = put(type1,type1_.);
do i=1 to dim(cols);
cols{i} = head1{i};
end;
output;
end;

if first.type2 then do;
flag = 2;
label = put(type2,type2_.);
do i=1 to dim(cols);
select;
when(type1=1 and type2=1) cols{i} = head2{i};
when(type1=1 and type2=2) cols{i} = head2{i};
when(type1=2 and type2=1) cols{i} = head3{i};
when(type1=2 and type2=2) cols{i} = head4{i};
when(type1=2 and type2=3) cols{i} = head4{i};
end;
end;
output;
flag = .;
end;

if first.type3 and type3 ^= 0 then do;
flag = 3;
label = put(type3,type3_.);
do i=1 to dim(cols);
cols{i} = head1{i};
end;
output;
flag = .;
end;

label = put(line,label.);
do i=1 to dim(cols);
cols{i} = _cols{i};
end;
output;
run;

* Proof of Concept for traffic lighting ;
proc format;
value traffica
0 -< 5 = "red"
5 -< 10 = "yellow"
30 - high = "green"
other = "white"
;
* dummy format for testing ;
value $usrname
"ABC" = "Alpha Charlie"
"DEF" = "Delta Foxtrot"
;
run;

* Now create final report ;
%macro code;
options nocenter nobyline nonumber nodate papersize="A4" orientation=portrait;
ods listing close;
ods escapechar="~";
ods pdf file="&path\&word..pdf" notoc uniform;

title
font=Arial
height=9pt
j=left
"Report 1 - Page ~{thispage}"
j=center
bold
"PERFORMANCE MANAGEMENT REPORT"
j=right "%sysfunc(today(),monname3.)-%sysfunc(today(),year2.)"
;

proc report
data=report
noheader
nowd
style(column)=[font=("Arial",8pt) cellpadding=0 cellspacing=0 cellwidth=15mm]
;
by userid;
where userid="&word";

columns userid page type1--type3 skill team flag label col1-col8;
define userid / order noprint order=internal;
define page / order noprint order=internal;
define type1 / order noprint order=internal;
define type2 / order noprint order=internal;
define type3 / order noprint order=internal;
define skill / display noprint;
define team / display noprint;
define flag / display noprint;
define label / display left width=50 spacing=0 style=[font_size=7pt cellwidth=76mm];
define col1 / display right width=8;
define col2 / display right width=8;
define col3 / display right width=8;
define col4 / display right width=8;
define col5 / display right width=8;
define col6 / display right width=8;
define col7 / display right width=8;
define col8 / display right width=8 style=[background=traffica.];

compute before _page_ / style=[background=LightYellow];
string = cat(
'~S={just=left}','Name: ',put(userid,$usrname.),'~S={}',
'~S={just=center}','Skill Set: ',skill,'~S={}',
'~S={just=right}','Team: ',team,'~S={}'
);

line string $200.;
endcomp;

compute flag;
select(flag);
when(1) /* type1 */
call define(_row_,'style','style=[font_size=10pt font_weight=bold background=Cyan]');
when(2) /* type2 */
call define(_row_,'style','style=[background=LightGrey]');
when(3) /* type3 */
call define(_row_,'style','style=[font_style=italic background=Pink]');
otherwise
;
end;
endcomp;

compute col1;
select(col1);
when(.A)
call define(_row_,'style','style=[vjust=bottom cellheight=9mm]');
when(.I)
call define(_row_,'style','style=[vjust=bottom cellheight=18mm]');
otherwise;
end;
endcomp;

break after page / page;

compute after userid;
line @5 " ";
line @5 "&btype1_1";
line @5 "&btype2_1";
line @5 "&label1";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 "&label2";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 "&btype2_3";
line @5 "&label3";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 "&label4";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 "OVERALL COMMENTS/ACTIONS AGREED";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 " ";
line @5 30*"_" @50 30*"_";
line @5 "MSA Signature" @50 "Team Leader Signature";
endcomp;
quit;

ods _all_ close;
ods listing;
title;
footnote;
%mend;

/*----------------------------------------------------------------------
* PROGRAM: loop
* DESCRIPTION: A "wrapper" macro to execute code over a list of items
* INPARMS: None
* DATE: 24/04/2006
* AUTHOR: SNB
*
* MODIFICATIONS:
* =============
* Date UID Request Description
* ---------- --- ---------- ------------------------------------------
* 24/04/2006 SNB N/A Initial revision
----------------------------------------------------------------------*/

%macro loop
/*----------------------------------------------------------------------
Invoke the nested macro "%code" over a list of space or comma separated
list of items.
----------------------------------------------------------------------*/
(list /* Space or comma separated list of items (REQ) */
);

/*----------------------------------------------------------------------
Usage:

%macro code;
%put &word;
%mend;
%loop(Hello World);

%let str = Hello,World;
%loop(%bquote(&str));

%macro code;
proc print data=&word;
run;
%mend;
data one;x=1;run; data two;y=2;run;
proc sql noprint;
select memname into :list separated by ','
from dictionary.tables
where libname = "WORK" and memtype = "DATA"
;
quit;
%loop(%bquote(&list));
------------------------------------------------------------------------
Notes:

The nested macro "%code" must be created at run time before calling
this macro.

Use the macro variable "&word" within your %code macro for each token
(word) in the input list.
----------------------------------------------------------------------*/

%local macro parmerr;

%* error checking ;
%let macro = &sysmacroname;
%*parmv(list,_req=1,_words=1,_case=N)
%*if (&parmerr) %then %goto quit;

%* make sure the iterator is unique ;
%* if %code resets the iterator problems occur ;
%let iter = _%substr(&sysprocessid,1,6)_;
%local &iter;

%let &iter = 1;
%let word = %scan(%superq(list),&&&iter,%str( ,));
%do %while (%superq(word) ne %str());
%code
%let &iter = %eval(&&&iter+1);
%let word = %scan(%superq(list),&&&iter,%str( ,));
%end;

%quit:
%mend;

proc sql noprint;
select
distinct userid into :list separated by ' '
from
report
;
quit;

options mprint spool;
%loop(&list)

[Tim_SAS]

Hi Scott!

Your question is difficult for me to answer. In general I'd recommend PROC REPORT because it's so much easier to use than raw DATA _NULL_ programming. However, PROC REPORT is really designed to make reports with input from data sets and that have rows and columns. Page 2 of your report doesn't fit into this mold and so you're having to do a lot of work for a less-than-100%-satisfactory result. Perhaps you can use PROC REPORT for pages 1 and 3 and the FILE and PUT statements for ODS in a data step for page 2?

Regarding the flag=2 rows, I notice that both the COMPUTE FLAG block and the COMPUTE COL1 block use the CALL DEFINE statement to define a style for the row. Since the COMPUTE COL1 block runs after the COMPUTE FLAG block, the style defined in the COMPUTE COL1 block replaces the style defined in the COMPUTE FLAG block.