Proc Report and creating different formats for each line statement

[tgleghorn]

Hello,

I am using proc report to write out to an xlsx workbook.   There are 5 footnotes that are required at the bottom left of the worksheet.  All 5 lines require a different format (background color, font color, font style).   The / style statement does not work.. I am just getting errors.

compute after population ;
length text1 text2 text3 text4 text5 $100;
if population in ('SCO','NHMCR') then do;
text1 = '* = STARS Clinical Outcomes Measure';
text2 = '* CDC Poor Control is an inverse measure - lower is better';
text3 = 'Dark yellow indicates current rate is better than MY 2022 final rate.';
text4 = 'Bold measures - triple weighted';
text5 = 'TRC - 2 Star weights - Average of submeasure and Med Rec';
end;
else if population in ('MH','NH') then do;
text1 = '*-Triple weighted measure';
text2 = 'Withhold measure';
text3 = '';
text4 = '';
text5 = '';
end;
else do;
text1 = ' ';
text2 = ' ';
text3 = ' ';
text4 = ' ';
text5 = ' ';
end;

  /** each of these line statements need a different format **/
line text1 $char100. / style = [&FlagStyle just=left];
line text2 $char100.;
line text3 $char100.;
line text4 $char100.;
line text5 $char100.;
endcomp;

[ballardw]

Data, in the form of a working data step.

Complete rest of the Procedure. It is entirely too easy for use to make a guess about where something is in a report body and be incorrect. Resulting in solutions that will not actually work.

List of the properties for which "line". If we don't know what to create when how can we get any sort of solution?

And if you are going to place information in Macro variables then include the definitions of ALL the macro variables to be used and which line they are to be used with.

And best would be to provide an example of what the result should look like given the example data set.

[Ksharp]

ods excel file='c:\temp\temp.xlsx';
ods escapechar='~';
proc report data=sashelp.class nowd;
columns sex age weight;
define sex/group;
define age/display;
compute after sex/style={just=l};
line '~{style [foreground=green fontsize=12]xxxxxxxxxx} ~n~{style [foreground=blue fontweight=bold]yyyyyyyyy}';
endcomp;
run;
ods excel close;

[Cynthia_sas]

Hi, Another way to do this conditionally with multiple LINE statements, shown below using just 3 groups from SASHELP.CLASS:

I didn't use page breaks or BY groups or  put each age on a new sheet just so it would make all 3 groups visible in one screen shot.

  Here's the code I used:

ods excel file='c:\temp\style_line.xlsx'
    options(embedded_titles='yes');
ods escapechar='^';
  
proc report data=sashelp.class
  style(lines)={just=l font_weight=bold vjust=m}
  style(column)={protectspecialchars=off};
title 'The title';
where age in (11,12,13);
column age name sex height weight;
define age / order;
define name / order;
define sex / display;
define height / display;
define weight / display;
compute after age;
  length tline1 tline2 tline3 $100;
  if age = 11 then do;
     tline1 = "^{style[color=red]Line 1 in PROC REPORT} (*ESC*){NEWLINE}" ;
	 lg1 = length(tline1);
     tline2 = "^{style[color=purple]Line 2 in PROC REPORT} (*ESC*){NEWLINE}" ;
	 lg2 = length(tline2);
     tline3 = "^{style[color=turquoise]Line 3 in PROC REPORT} (*ESC*){NEWLINE}" ;
	 lg3 = length(tline3);
  end;
  else if age = 12 then do;
     tline1 = "^{style[color=black]Line 1 in PROC REPORT} (*ESC*){NEWLINE}" ;
	 lg1 = length(tline1);
     tline2 = "^{style[color=black]Have Line 2 but No Line 3}" ;
	 lg2 = length(tline2);
     tline3 = ' ';
	 lg3 = 0;
  end;
  else if age = 13 then do;
     tline1 = "^{style[color=purple]Line 1 in PROC REPORT} (*ESC*){NEWLINE}" ;
	 lg1 = length(tline1);
     tline2 = "  (*ESC*){NEWLINE}";
	 lg2 = length(tline2);
     tline3 = "^{style[color=red]Line 3 (blank for line 2)}" ;
	 lg3 = length(tline3);
  end;
  line tline1 $varying. lg1;
  line tline2 $varying. lg2;
  line tline3 $varying. lg3;
endcomp;
run;
ods excel close;

Hope this helps,

Cynthia

[tgleghorn]

How do I put the style with the actual text. tline1 = 'bold measures - triple weighyed'; would it be tline1 = "Bold measures - triple weighyed ^{style[color=purple]Line 1 in PROC REPORT} (*ESC*){NEWLINE}" ;

[tgleghorn]

Duh.. I just saw in your code where to put the actual text. my bad.

[tgleghorn]

it is ignoring my background colors.

compute after population ;

if population in ('SCO','NHMCR') then do;
text1 = "^{style[background=black color=white] * = STARS Clinical Outcomes Measure} (*ESC*){NEWLINE}" ;
lg1 = length(text1);
text2 = "^{style[background=grey color=black] * CDC Poor Control is an inverse measure - lower is better} (*ESC*){NEWLINE}";
lg2 = length(text2);
text3 = "^{style[background=yellow color=black] Dark yellow indicates current rate is better than MY 2022 final rate.} (*ESC*){NEWLINE}";
lg3 = length(text3);
text4 = "^{style[color=black] Bold measures - triple weighted } (*ESC*){NEWLINE}";
lg4 = length(text4);
text5 = "^{style[color=black] TRC - 2 Star weights - Average of submeasure and Med Rec} (*ESC*){NEWLINE}";
lg5 = length(text5);
end;
else if population in ('MH','NH') then do;
text1 = "^{style[color=black] *-Triple weighted measure(*ESC*){NEWLINE}";
lg1 = length(text1);
text2 = "^{style[color=black] Withhold measure} (*ESC*){NEWLINE}";
lg2 = length(text2);
text3 = "^{style[color=black] } (*ESC*){NEWLINE}";
lg3 = length(text3);
text4 = "^{style[color=black] } (*ESC*){NEWLINE}";
lg3 = length(text4);
text5 = "^{style[color=black] } (*ESC*){NEWLINE}";
lg5 = length(text5);
end;
else do;
text1 = ' ';
lg1 = length(text1);
text2 = ' ';
lg2 = length(text2);
text3 = '';
lg3 = length(text3);
text4 = '';
lg3 = length(text4);
text5 = '';
lg5 = length(text5);
end;
line text1 $varying. lg1;
line text2 $varying. lg2;
line text3 $varying. lg3;
line text4 $varying. lg4;
line text5 $varying. lg5;

endcomp;

[tgleghorn]

This is what I am trying to accomplish with the line statements. I have everything working for each sheet except for the formatting of the line statements. The code that was provided as a solution appears to work except for the background color is not working.

[tgleghorn]

the style statement does not work on line statements.

[ballardw]

I think in Proc Report Backgroundcolor is more of a CELL property. Which would mean you can't mix background colors.

Separate compute blocks for different line perhaps.

You have also not provided enough code or data to allow us to even test code, making much harder to provide a solution that may work.

---

@tgleghorn wrote:
the style statement does not work on line statements.

---

[Cynthia_sas]

Yes, That's correct. The multiple LINE statements are all writing into one big cell. So you cannot have a different background color for each line. That's why I only changed the foreground color. It is the same in PDF and RTF -- what is in one cell, no matter how many lines has one background color for the whole cell.

Cynthia

[Ksharp]

You need to produce another DUMMY variable to get another LINE statement to produce TWO LINES.

data have;
 set sashelp.class;
 dummy=ifn(sex='F',1,2);
run;

ods excel file='c:\temp\temp.xlsx';
ods escapechar='~';
proc report data=have nowd;
columns dummy sex age weight;
define dummy/order noprint;
define sex/order;
define age/display;
compute after sex/style={just=l foreground=green background=yellow fontsize=12};
line 'xxxxxxxxxx';
endcomp;
compute after dummy/style={just=l foreground=blue background=red};
line 'yyyyyyyyy';
endcomp;
run;
ods excel close;

[tgleghorn]

will give this a try...  I separated worksheets for each population and the formatting is different for all 4 worksheets. 

proc report data=Dashboard_&filedate.(drop=rate rate_pw rate_f rate_py) split='#'

style(header)=[background=&hdr. color=&fnt.]

        style(lines)={just=l font_weight=bold}

;

where population="&pop.";

columns ("&pop1. Weekly Hybrid Rates Dashboard" (population description measurekey denom_f numer_f rate_f /*rate_i*/)) 

("QMRM Data &filedate." (denom mrnum numer rate))

("&curyr. Cut Points" (cutpt1 cutpt2 cutpt3 cutpt4))

('Num Hits Needed to Reach' (hits1 hits2 hits3 hits4))

("QMRM Data &pfiledat. " (denom_pw mrnum_pw numer_pw rate_pw rate_chg_pw))

("QMRM Data &pywk."   (denom_py mrnum_py numer_py rate_py rate_chg_py)) 

            line0 line1 line2 line3 line4 line5;

        define population / order noprint;

        define description / display  style(header)= [background=gray color=White] "MY&curyr. Measure Description";

define measurekey / display style(header)= [background=gray color=White] 'Measure#Key';

define denom_f / analysis noprint;

define numer_f / analysis noprint;

define rate_f / computed format=percent8.2 style(header)= [background=gray color=White] "MY&pmyr.#Reported#Rate";

*define rate_i / computed format=percent8.2 style(header)= [background=gray color=White] "Initial#Admin#Rate#MY&cmyr";

define denom /  analysis format=8. style(header)= [background=gray color=White] 'Den';

define mrnum / analysis  format=8. style(header)= [background=gray color=White] 'Med Rec#Num';

define numer / analysis format=8. style(header)= [background=gray color=White] 'Num';

define rate / computed format=percent8.2 style(header)= [background=gray color=White] 'Rate';

define cutpt1 / analysis format=percent8.2 style(header)= [background=gray color=White];

define cutpt2 / analysis format=percent8.2 style(header)= [background=gray color=White];

define cutpt3 / analysis format=percent8.2 style(header)= [background=gray color=White];

define cutpt4 / analysis format=percent8.2 style(header)= [background=gray color=White];

define hits1 / analysis format=8. style(header)= [background=gray color=White] '2_Stars';

define hits2 / analysis format=8. style(header)= [background=gray color=White] '3_Stars';

define hits3 / analysis format=8. style(header)= [background=gray color=White] '4_Stars';

define hits4 / analysis format=8. style(header)= [background=gray color=White] '5_Stars';

define denom_pw / analysis format=8. style(header)= [background=gray color=White] 'Den';

define mrnum_pw / analysis format=8. style(header)= [background=gray color=White] 'Med Rec#Num';

define numer_pw / analysis format=8. style(header)= [background=gray color=White] 'Num';

define rate_pw / computed format=percent8.2 style(header)= [background=gray color=White] "Prior#Week#MY&cmyr.#Rate";

define rate_chg_pw / computed format=percent8.2 style(header)= [background=gray color=White] "Chg from#MY&cmYR.#Prior#Week";

define denom_py / noprint;

define mrnum_py / noprint;

define numer_py / noprint;

define rate_py / computed format=percent8.2 style(header)= [background=gray color=White] "Prior Year#MY&pmyr. Rate#Same Week";

define rate_chg_py / computed format=percent8.2 style(header)= [background=gray color=White] "Chg from#MY&pmyr.#Same#Week";

compute rate_f;

rate_f=numer_f.sum/denom_f.sum;

if numer_f.sum/denom_f.sum le numer.sum/denom.sum then

call define(_col_,'style', "style=[background=&hdr. font_weight=bold]");

else call define(_col_,'style', "style=[background=&tab.]");

endcomp;

compute rate;

rate=numer.sum/denom.sum;

if numer.sum/denom.sum ge numer_f.sum/denom_f.sum then

call define(_col_,'style', "style=[background=&hdr. font_weight=bold]");

else call define(_col_,'style', "style=[background=&tab.]");

endcomp;

compute hits1;

if hits1.sum=0 then

call define(_col_,'style', 'style=[background=dark gray]');

endcomp;

compute hits2;

if hits2.sum=0 then

call define(_col_,'style', 'style=[background=dark grey]');

endcomp;

compute hits3;

if hits3.sum=0 then

call define(_col_,'style', 'style=[background=dark grey]');

endcomp;

compute hits4;

if hits4.sum=0 then

call define(_col_,'style', 'style=[background=dark grey]');

endcomp;

compute rate_pw;

rate_pw=numer_pw.sum/denom_pw.sum;

if numer_pw.sum/denom_pw.sum le numer.sum/denom.sum then

call define(_col_,'style', "style=[background=&hdr. font_weight=bold]");

else call define(_col_,'style', "style=[background=&tab.]");

endcomp;

compute rate_chg_pw;

rate_chg_pw=(numer_pw.sum/denom_pw.sum) - (numer.sum/denom.sum);

endcomp;

compute rate_py;

rate_py=numer_py.sum/denom_py.sum;

if numer_py.sum/denom_py.sum le numer.sum/denom.sum then

call define(_col_,'style', "style=[background=&hdr. font_weight=bold]");

else call define(_col_,'style', "style=[background=&tab.]");

endcomp;

compute rate_chg_py;

rate_chg_py=(numer_py.sum/denom_py.sum) - (numer.sum/denom.sum);

endcomp;

compute after population;

    if population in ('SCO','NHMCR') then do;

      text1 = "^{style[background=black foreground=white] * = STARS Clinical Outcomes Measure} (*ESC*){NEWLINE}" ;

  lg1 = length(text1);

      text2 = "^{style[background=grey color=black] * CDC Poor Control is an inverse measure - lower is better} (*ESC*){NEWLINE}";

      lg2 = length(text2);

      text3 = "^{style[background=yellow color=black] Dark yellow indicates current rate is better than MY 2022 final rate.} (*ESC*){NEWLINE}";

      lg3 = length(text3);

      text4 = "^{style[color=black] Bold measures - triple weighted } (*ESC*){NEWLINE}";

  lg4 = length(text4);

      text5 = "^{style[color=black] TRC - 2 Star weights - Average of submeasure and Med Rec} (*ESC*){NEWLINE}";

  lg5 = length(text5);

    end;

else if population in  ('MH','NH') then do;

  text1 = "^{style[color=black] *-Triple weighted measure(*ESC*){NEWLINE}";

  lg1 = length(text1);

      text2 = "^{style[color=black] Withhold measure} (*ESC*){NEWLINE}";

  lg2 = length(text2);

  text3 = "^{style[color=black]  } (*ESC*){NEWLINE}";

  lg3 = length(text3);

  text4 = "^{style[color=black]  } (*ESC*){NEWLINE}";

  lg3 = length(text4);

  text5 = "^{style[color=black]  } (*ESC*){NEWLINE}";

  lg5 = length(text5);

end;

    else do;

      text1 = ' ';

  lg1 = length(text1);

      text2 = ' ';

  lg2 = length(text2);

    text3 = '';

  lg3 = length(text3);

  text4 = '';

  lg3 = length(text4);

  text5 = '';

  lg5 = length(text5);

    end; 

    line text1 $varying. lg1;

    line text2 $varying. lg2;

line text3 $varying. lg3;

line text4 $varying. lg4;

line text5 $varying. lg5;

endcomp;

run;

[tgleghorn]

creating the dummy worked.   now just have to limit it to the one cell 🙂