BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ECO918
Obsidian | Level 7

Greetings- I'm trying to output tables to an RTF file.

 

My issue is that the RTF output doesn't include the conditional color coded background (see compute block, format= oe.), however it works in other tables...so I'm stumped. I've stared at it a 1000 times and can't figure out why. My code is attached. Any help is appreciated.

 

As long as I'm at it, what RTF file is most similar to HTML output?SAS html and rtf output.png

/* PROC REPORT */

ods rtf file= "new.rtf" startpage= no startpage=no style= yourstyle_v2;
	ods escapechar='^';
	proc report data=new SPANROWS 
	style(header)=[borderrightwidth=0 borderbottomwidth=2 borderbottomcolor= black backgroundcolor=white/*cxe7eff6*/ color=black bordertopwidth=2 bordertopcolor=black]
	style(report)={frame=void};
	column Order_var Location Value ('FY 2014' FY14Q1 FY14Q2 FY14Q3 FY14Q4) ('FY 2015' FY15Q1 FY15Q2 FY15Q3 FY15Q4);
		define Order_var/ order noprint;
		define Location / " " group style=[fontweight=bold borderbottomwidth=2 borderbottomcolor=black borderrightwidth=0];
		define value / display " " center style(column)=[borderrightwidth=0 cellwidth=0.75in];
		define FY14Q1/ "Q1" center style(column)=[borderrightwidth=0 cellwidth=0.75in] format=noblanks.;
		define FY14Q2/ "Q2" center style(column)=[borderrightwidth=0 cellwidth=0.75in];
		define FY14Q3/ "Q3" center style(column)=[borderrightwidth=0 cellwidth=0.75in];	
		define FY14Q4/ "Q4" center style(column)=[borderrightwidth=1 cellwidth=0.75in];
		define FY15Q1/ "Q1" center style(column)=[borderrightwidth=0 cellwidth=0.75in];
		define FY15Q2/ "Q2" center style(column)=[borderrightwidth=0 cellwidth=0.75in];
		define FY15Q3/ "Q3" center style(column)=[borderrightwidth=0 cellwidth=0.75in];
		define FY15Q4/ "Q4" center style(column)=[borderrightwidth=0 cellwidth=0.75in];
		compute value;
			if value='O/E' then call define (_row_, "style", "Style=[background=oe. font_weight=bold borderbottomwidth=2 borderbottomcolor=black]");
		endcomp;
	run;
ods rtf close;

/* TEMPLATE */
proc template;
define style yourstyle_v2;
parent = Styles.rtf;
class fonts /
'TitleFont' = ("Times New Roman, Times Roman, Times" ,15pt,Bold )
'TitleFont2' = ("Times New Roman, Times Roman, Times" ,15pt,Bold )
'footFont' = ("Times New Roman, Times Roman, Times" ,9pt,Bold )
'StrongFont' = ("Times New Roman, Times Roman, Times" ,11pt,Bold)
'EmphasisFont' = ("Times New Roman, Times Roman, Times" ,11pt,Italic)
'headingEmphasisFont' = ("Times New Roman, Times Roman, Times" ,11pt,Bold)
'headingFont' = ("Times New Roman, Times Roman, Times" ,11pt,Bold)
'docFont' = ("Times New Roman, Times Roman, Times" ,11pt)
'FixedEmphasisFont' = ("Times New Roman, Times Roman, Times" ,15pt,italic)
'FixedStrongFont' = ("Times New Roman, Times Roman, Times" ,15pt,Bold)
'FixedHeadingFont' = ("Times New Roman, Times Roman, Times" ,15pt,Bold)
'BatchFixedFont' = ("Times New Roman, Times Roman, Times" ,15pt)
'FixedFont' = ("Times New Roman, Times Roman, Times" ,15pt)
;
class color_list /
'link'= blue
'bgH' = white
'bgT' = white
'bgD' = white
'fg' = black
'bg' = white
;
class systemtitle /
protectspecialchars=OFF
asis=ON
;
class systemfooter /
font=Fonts('footFont')
protectspecialchars=OFF
asis=ON
;
class header /
protectspecialchars=off
;
class data /
protectspecialchars=off
;
class rowheader /
protectspecialchars=off
;
class usertext /
protectspecialchars=off
;
class byline /
protectspecialchars=off
;
class header/
backgroundcolor=#f0f0f0
;
class parskip/
fontsize= 1pt;
end;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  After eliminating everything else (template, custom style, etc) and making my own data and format (I had to use GROUP for VALUE) because of my data, the following code works for me in HTML, RTF and PDF.

 

  So there has to be something else going on in your code. And, since you haven't shown ALL of your code (you don't show the format creation code), it is hard to comment on your code. But my code, as written, should work for you.

 

cynthia

 

data testit;
  set sashelp.prdsale;
  where year = 1993 and quarter = 1 and division = 'CONSUMER'
        and region = 'EAST' and product in ('BED', 'CHAIR', 'TABLE');
  order_var = country;
  location = region;
  value = product;
  act1 = actual / 100;
  act2 = actual / 13;
  act3 = actual / 77;
  prd1 = predict / 100;
  prd2 = predict / 13;
  prd3 = predict / 77;
run;

proc format;
  value oe 0-<20 = 'lightpink'
           20<-100 = 'lightyellow'
           100<-high = 'lightgreen';
run;
ods _all_ close;

ods rtf file='c:\temp\testcolor.rtf';
ods html file='c:\temp\testcolor.html';
ods pdf file='c:\temp\testcolor.pdf';
title 'Testing color coding';
proc report data=testit nowd;
 
column Order_var Location Value ('FY 2014'act1 act2 act3) ('FY 2015' prd1 prd2 prd3 );
		define Order_var/ group ;
		define Location / " " group style=[fontweight=bold   ];
		define value / group " " center style(column)=[ cellwidth=0.75in];
		define act1/ "Q1" center style(column)=[ cellwidth=0.75in] f=6.2;
		define act2/ "Q2" center style(column)=[ cellwidth=0.75in] f=6.2;
		define act3/ "Q3" center style(column)=[  cellwidth=0.75in] f=6.2;	
		define prd1/ "Q1" center style(column)=[  cellwidth=0.75in] f=6.2;
		define prd2/ "Q2" center style(column)=[  cellwidth=0.75in] f=6.2;
		define prd3/ "Q3" center style(column)=[  cellwidth=0.75in] f=6.2;
		compute value;
			if value='TABLE' then call define (_row_, "style", "Style=[background=oe. font_weight=bold]");
		endcomp;
	run;
ods _all_ close;

Produced this output:

test_color_coding.png

 

cynthia

View solution in original post

6 REPLIES 6
ballardw
Super User

Since your custom style is based on Styles.RTF you should be getting bits in the log about color elements not defined as the base Style.RTF doesn't have most of the color elements. So your style overrides do not have anything to override.

 

Have you tried your output with a different style?

ECO918
Obsidian | Level 7

Thanks for your response!

 

The weird thing is that the same style works just fine for other tables, just not this one. The log doesn't say anything other than the number of read observations and processing time. I attached a picture of a dummy table where the conditional formatting worked in the RTF output, and the code. I originally thought it was the compute statement, but even when I use the format in a define statement, it still doesn't appear in the RTF output.

 

Also, I have a typo above- what RTF style is closest to html output?
SAS rtf vs html that works 2.png

ods rtf file= "new.rtf" startpage= no startpage=no style= yourstyle_v2;
proc report data= traffic_light 	headline headskip nowd split = '\'
				style=[cellspacing=0 cellpadding=0]
				style(column)=[cellspacing=10];
column A B C D;
define A/ display center style={background= $traffic_lighttwo. cellwidth= 4cm};
define B/ display center style={background= $traffic_lighttwo. cellwidth= 4cm};
define C/ display center style={background= $traffic_lighttwo. cellwidth= 4cm};
define D/ display center style={background= $traffic_lighttwo. cellwidth= 4cm};
run;
ods rtf close;

 


SAS rtf vs html that works 2.png
ballardw
Super User

Try using the same style for your html. I do lots of RTF output and never use the RTF style because the fonts are generally bigger than I want.

If a specific table fails I would look into the details of your override codes. For instance when using a format for coloring are the ranges in the format actually valid for the specific cell? If your formats use boundaries such as

 

value myvalue

0 - 10.5 = 'red'

10.6 - 12 = 'orange'

;

 

Then values between 10.500 and 10.600 have no formatted value and might want 10.5 < - 12.

Other things would be to look at: are the formats available? If they aren't in a permanent library in the current format search path then they won't render. (or the format code needs to be rerun and check for errors such that the format wasn't actually created) .

 

 

ECO918
Obsidian | Level 7

Great troubleshooting advice, thank you!

 

I tried breaking the problem down, but I still can't figure out why the color coding doesn't work just for the selected table, because it works fine for other proc report rtf output. I'll keep playing with it though!

Cynthia_sas
SAS Super FREQ

Hi:

  After eliminating everything else (template, custom style, etc) and making my own data and format (I had to use GROUP for VALUE) because of my data, the following code works for me in HTML, RTF and PDF.

 

  So there has to be something else going on in your code. And, since you haven't shown ALL of your code (you don't show the format creation code), it is hard to comment on your code. But my code, as written, should work for you.

 

cynthia

 

data testit;
  set sashelp.prdsale;
  where year = 1993 and quarter = 1 and division = 'CONSUMER'
        and region = 'EAST' and product in ('BED', 'CHAIR', 'TABLE');
  order_var = country;
  location = region;
  value = product;
  act1 = actual / 100;
  act2 = actual / 13;
  act3 = actual / 77;
  prd1 = predict / 100;
  prd2 = predict / 13;
  prd3 = predict / 77;
run;

proc format;
  value oe 0-<20 = 'lightpink'
           20<-100 = 'lightyellow'
           100<-high = 'lightgreen';
run;
ods _all_ close;

ods rtf file='c:\temp\testcolor.rtf';
ods html file='c:\temp\testcolor.html';
ods pdf file='c:\temp\testcolor.pdf';
title 'Testing color coding';
proc report data=testit nowd;
 
column Order_var Location Value ('FY 2014'act1 act2 act3) ('FY 2015' prd1 prd2 prd3 );
		define Order_var/ group ;
		define Location / " " group style=[fontweight=bold   ];
		define value / group " " center style(column)=[ cellwidth=0.75in];
		define act1/ "Q1" center style(column)=[ cellwidth=0.75in] f=6.2;
		define act2/ "Q2" center style(column)=[ cellwidth=0.75in] f=6.2;
		define act3/ "Q3" center style(column)=[  cellwidth=0.75in] f=6.2;	
		define prd1/ "Q1" center style(column)=[  cellwidth=0.75in] f=6.2;
		define prd2/ "Q2" center style(column)=[  cellwidth=0.75in] f=6.2;
		define prd3/ "Q3" center style(column)=[  cellwidth=0.75in] f=6.2;
		compute value;
			if value='TABLE' then call define (_row_, "style", "Style=[background=oe. font_weight=bold]");
		endcomp;
	run;
ods _all_ close;

Produced this output:

test_color_coding.png

 

cynthia

ECO918
Obsidian | Level 7

Hi Cynthia,

 

Thanks for your feedback! I used your code and worked backwards to figure out where mine went wrong. You were correct, it was an issue with my format code. I was using hex color codes and didn't include the 'CX' tag. The absence of the 'CX' didn't affect the html output, but it did affect the rtf output. The original and new code are below. Many, many thanks!

 

Original - doesn't work:

  proc format;

  value oe 0-<1 = 'e1fbd4'

           1<-100 = 'ffcccc';

  run;

 

New - works fine:

  proc format;

  value oe 0-<1 = 'CXe1fbd4'

           1<-100 = 'CXffcccc';

  run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2045 views
  • 3 likes
  • 3 in conversation