The SAS Output Delivery System and reporting techniques

Proc Report and excelxp tagset formatting issues

Reply
Contributor
Posts: 53

Proc Report and excelxp tagset formatting issues

Hi all,

I'm using proc report and the excelxp tagset to generate a report. I'm having 2 really odd formatting issues. 1st, here's my code:
PROC REPORT nowd DATA=&in_dataset MISSING nowindows SPLIT='*' HEADLINE contents = ''
style(header)={font_weight=bold font_face="Franklin Gothic Medium" background=CXDBDEE1 foreground=black}
style(column)={background=white font_face="Franklin Gothic Medium"}
;
by attorney;
column count attorney name product status;
define count / order noprint;
define attorney / order noprint;
define product / 'Product';
define status / 'Status';

break before count / contents = "" page;

compute after attorney / style={background=RED};
line @1 'Total # of Claims: ' n comma6.;
endcomp;

title color = CX00447C font="Franklin Gothic Medium" h=14pt
"Data As Of: " &asofdate #byval(attorney);
run;

Issue 1) No matter what I do I cannot get the summary line (outputted in the compute after attorney) to have a red background or format in any way. Why isn't the style attribute working?

Issue 2) I get an extra line between the title and the top of the report and also right after the summary line mentioned in issue 1. For some reason only the 1st cell in these rows has the same background as the title and summary line.. which looks silly. Almost like the title and summary line are spilling over into the 1st cell of the next row...
SAS Super FREQ
Posts: 8,868

Re: Proc Report and excelxp tagset formatting issues

Hi:
Can you show ALL your code -- including the ODS invocation and the options/styles that you are using???

Also, are you using embedded_titles='yes' as a suboption? And, you show the use of the variable N in your LINE statement -- but I don't see where N is calculated???

cynthia
Contributor
Posts: 53

Re: Proc Report and excelxp tagset formatting issues

Posted in reply to Cynthia_sas
Hey Cynthia, sorry, Every time I post my complete code the end is chopped off...

ods tagsets.ExcelXP path="&out_path" file="&out_file"
options(absolute_column_width="25,5,7,5,8,3,6,3,5,6,5,6,5,6,5"
embedded_titles='yes'
sheet_interval='bygroup'
sheet_label='none'
sheet_name='none'
suppress_bylines='yes'
zoom='85'
orientation='landscape'
fittopage='yes'

PROC REPORT nowd DATA=&in_dataset MISSING nowindows SPLIT='*' HEADLINE contents = ''
style(header)={font_weight=bold font_face="Franklin Gothic Medium" background=CXDBDEE1 foreground=black}
style(column)={background=white font_face="Franklin Gothic Medium"}
;
by attorney;
column count attorney name product status account_nbr city state pacman_claim_number
policy_year reserve
('Next*Upcoming Tickler' date_of_tickler_max userid)
('Last*Reserve Review*Diary' date_of_res_rev_max days_since_last_res_rev ) days_since_last_res_rev_color
('Last*Diary Record' date_of_diary_max days_since_last_diary) days_since_last_diary_color n ;

define count / order noprint;
define attorney / order noprint;
define product / 'Product';
define status / 'Status';
define account_nbr / 'Account';
define name / 'Account Name';
define city / 'City';
define state / 'State';
define pacman_claim_number / 'Claim Number';
define policy_year / 'Pol*Year';
define date_of_tickler_max / 'Date' format=mmddyy10.;
define userid / 'Created*By' ;
define reserve / 'Reserve' format=dollar12.;
define date_of_res_rev_max / 'Date' format=mmddyy10.;
define days_since_last_res_rev / '# Days' format=comma6.;
define date_of_diary_max / 'Date' format=mmddyy10.;
define days_since_last_diary / '# Days' format=comma6.;
define n / noprint;
define days_since_last_res_rev_color / computed noprint;
define days_since_last_diary_color / computed noprint;

break before count / contents = "" page;

compute days_since_last_res_rev_color;
.....
endcomp;

compute days_since_last_diary_color;
.....
endcomp;

compute after attorney/style={background=RED};
line @1 'Total # of Claims: ' n comma6.;
endcomp;
title color = CX00447C font="Franklin Gothic Medium" h=14pt
"All Open Claims Detail by Attorney As Of: " &asofdate #byval(attorney);
run;
SAS Super FREQ
Posts: 8,868

Re: Proc Report and excelxp tagset formatting issues

Hi:
Just curious: are you seeing this message in the log:
[pre]
WARNING: The TITLE statement is ambiguous due to invalid options or
unquoted text.
[/pre]

You need to have quotes around ALL your title statement:
[pre]
title color = CX00447C font="Franklin Gothic Medium" h=14pt
"All Open Claims Detail by Attorney As Of: &asofdate #byval(attorney)";
[/pre]

The macro variable and the #byval will resolve inside double quotes just fine.

As for your question about the LINE statement, it sticks in my mind that you may need to change the style template in order to alter the style of the LINES for TAGSETS.EXCELXP. If you look at the output generated by this code, the style of the LINES for the HTML output will be RED (as specified in the PROC REPORT statement) -- but for the TAGSETS.EXCELXP output, the style of the LINES will be CYAN (as specified in the STYLE template). The style template syntax shown is the 9.2 syntax with the CLASS statement.

cynthia
[pre]
proc sort data=sashelp.class out=class;
by sex age;
run;

ods listing close;
options nobyline;

ods path work.temp(update) sasuser.templat(update) sashelp.tmplmst(read);

proc template;
define style styles.myline;
parent=styles.sasweb;
class NoteContent/
background=cyan;
end;
run;

ods msoffice2k file='c:\temp\testsummary.html' style=styles.myline;
ods tagsets.excelxp file='c:\temp\testsummary.xls' style=styles.myline
options(embedded_titles='yes');

PROC REPORT nowd DATA=class MISSING nowindows SPLIT='*'
contents = ''
style(header)={font_weight=bold font_face="Franklin Gothic Medium" background=CXDBDEE1 foreground=black}
style(column)={background=white font_face="Franklin Gothic Medium"}
style(lines)={background=red}
style(summary)={background=yellow} ;

by sex;
column sex age name n height weight;
define sex / order ;
define age / order ;
define n / 'Count';
define height / display 'Product';
define weight / display 'Status';

break before age / contents = "" page;
break after age / summarize;

compute after age ;
line 'Total # of Claims: ' n comma6.;
endcomp;

title color = CX00447C font="Franklin Gothic Medium" h=14pt
"Data As Of: &sysdate9 #byval(sex)";
run;
ods _all_ close;
options byline;
[/pre]
Contributor
Posts: 53

Re: Proc Report and excelxp tagset formatting issues

Posted in reply to Cynthia_sas
Yep you were 100% correct about the warning I was getting and the fix worked perfectly!

Also, I created a custom template and defined all the styles myself so I can reuse it. This worked great! But how do I make this template avaiable to be shared by multiple reports?



ods path work.temp(update) sasuser.templat(update) sashelp.tmplmst(read);
proc template;
define style styles.abais_standard;

class systemtitle /
fontfamily = "Franklin Gothic Medium"
fontweight = bold
fontsize = 13pt
backgroundcolor = CXDBDEE1
color = CX00447C
bordercolor = white
borderstyle = solid
borderwidth = 1pt
;
class header /
fontfamily = "Franklin Gothic Medium"
fontsize = 12pt
backgroundcolor = CXDBDEE1
color = CX00447C
bordercolor = white
borderstyle = solid
borderwidth = 1pt
;
class table /
fontfamily = "Franklin Gothic Medium"
fontsize = 11pt
bordercolor = CXDBDEE1
borderstyle = solid
borderwidth = 1pt
;
class NoteContent /
fontfamily = "Franklin Gothic Medium"
fontsize = 12pt
backgroundcolor = CXDBDEE1
color = CX00447C
bordercolor = white
borderstyle = solid
borderwidth = 2pt
;
end;
run;
SAS Super FREQ
Posts: 8,868

Re: Proc Report and excelxp tagset formatting issues

You would have to write your template to a permanent library (possibly on a shared drive or a server drive). Then you and everybody else would need an ODS PATH statement that pointed to the template in that location. So, right now, the ODS PATH is writing to the WORK location. You would have to alter the ODS PATH to write to a permanent location.

Search in the doc for ODS PATH or look at the overview at the beginning of this paper:
http://support.sas.com/resources/papers/proceedings09/227-2009.pdf

cynthia
Contributor
Posts: 53

Re: Proc Report and excelxp tagset formatting issues

Posted in reply to Cynthia_sas
Youre awesome as usual! got that to work! Another issue (sigh)...

In the code I have a few compute blocks which traffic light cells in certian columns.. In my initial posting I removed the code as I didn't think it mattered.. here's the compute blocks:

compute days_since_last_diary_color;
days_since_last_diary_color = days_since_last_diary.sum;
if days_since_last_diary_color => 90 then do;
call define('_c17_','style','style=[background=CXDA533E foreground=white]');
call define('_c18_','style','style=[background=CXDA533E foreground=white]');
end;
endcomp;

I want these traffic lit cells to ahdere to my template except for the background and foreground colors which I override. What style elements in the template apply to cells like this?
SAS Super FREQ
Posts: 8,868

Re: Proc Report and excelxp tagset formatting issues

Hi:
Tabular output is very simple. It has column header cells most of the time. Since a table is not just composed of column headers -- so you table probably has data cells. It really doesn't matter whether you use PROC REPORT or PROC MEANS or PROC GLM or PROC WHATEVER ... all tabular output has header cells, data cells, possibly row header cells. There are probably titles in your tabular output, maybe footnotes, but the basic tabular output is pretty simple and doesn't use too many different style elements.

Not surprisingly, the column header cells are styled by the HEADER style element in your style template and the DATA style element willl control the style of the data cells. Any overrides that you put in your REPORT code should override what is coming from the STYLE template (and, as you saw in your original question -- sometimes a style template is the only way to impact output -- especially when using ODS TAGSETS.EXCELXP -- mostly because Microsoft XML and style is a little tricky sometimes).

You show only a snippet of code, out of context. Therefore, it is hard to understand what you are asking. Are you saying that the cells with trafficlighting do not adhere to the style template??? You are overriding both foreground color and background color -- that really only leaves the font to be controlled by the template.

If you are reluctant to show all your code, then pick SASHELP.CLASS or SASHELP.SHOES and see whether you can replicate your issue with a simplified, but complete version of code which illustrates your question or issue.. For example, consider the style template below and code that does trafficlighting.

The style template sets the font for ALL the data cells (the DATA style element) to be COURIER NEW and the size for ALL the data cells to be 12pt. All the other style characteristics will come from the SASWEB style. Then, PROC REPORT overrides only the background color, a simple override for AGE=15, using CALL DEFINE. When I view the output in Excel, ALL the data cells are displayed with COURIER NEW font, the HEADER cells are formatted as specified in the style template (the headers are blue and white). Only the background change shows for the cells where AGE=15 -- BUT the font of those cells is still in COURIER NEW and the foreground color is the same as all the other data cells.

Is there something about your output that implies the style template is not being used???

cynthia
[pre]
ods path work.tmp(update)
sashelp.tmplmst(read);

proc template;
define style styles.testtemp;
parent=styles.sasweb;
class Data /
font_face='Courier New'
font_size=12pt;
end;
run;

ods tagsets.excelxp file='c:\temp\test.xls'
style=testtemp;

proc report data=sashelp.class nowd;
column name sex age height;
define name/ order;
define sex / order;
define age / display;
define height / display;
compute age;
if age = 15 then
call define(_col_,'style','style={background=pink}');
endcomp;
run;
ods _all_ close;
[/pre]
Ask a Question
Discussion stats
  • 7 replies
  • 493 views
  • 0 likes
  • 2 in conversation