This gets a little closer:
%let ent_rb = foo;
data work.source;
infile datalines dlm='|';
length row1 row2 $150;
input row1 $ value1 $ row2 $ value2 $ ;
datalines;
Rate: Baseline| &ent_rb| | |
Rate: Current | | | |
Improvement| | | |
Superior Performance| | | |
Target Achieved:| | | |
;
run;
ods excel file="x:\data\junk.xlsx"
style=journal options(embedded_titles='yes'
embedded_footnotes='yes' sheet_interval='NONE'
sheet_name="text. Summary"absolute_column_width='26,12,26,12' );
proc report data=work.source spanrows nowd
style(header)={bordertopwidth=.5 borderbottomwidth=.5
borderleftwidth=.5 borderrightwidth=.5 borderstyle=solid
bordercolor=black}
;
column ("Level 2" (row1 value1 row2 value2));
define row2 / " " style(column)={};
define row1 / " " style(column)={ bordertopwidth=.5 borderbottomwidth=.5
borderleftwidth=.5 borderrightwidth=.5 borderstyle=solid
bordercolor=black};
define value1 / " " style={ borderwidth=0
borderstyle=solid
bordercolor=black};
define value2 / " " style={ borderwidth=0
};
compute value1;
if row1 = 'Target Achieved: Y' then do;
call define('row1', 'style', 'style=[background=lightgreen font_weight=bold just=c]');
call define('value1', 'style', 'style=[background=lightgreen font_weight=bold just=c]');
call define('row2', 'style', 'style=[borderbottomwidth=.5]');
end;
else if row1 = 'Target Achieved: N' then do;
call define('row1', 'style', 'style=[background=lightred font_weight=bold just=c]');
call define('value1', 'style', 'style=[background=lightred font_weight=bold just=c]');
call define('row2', 'style', 'style=[borderbottomwidth=.5]');
end;
endcomp;
quit;
ods excel close;
To get your "Target Achieved" to span columns as you show in desired is beyond what I can' do at the moment.
A big cause of your extra lines was in the compute block for Value1.
... View more
