The SAS Output Delivery System and reporting techniques

Issues while using ODS Excel XP in EG

Reply
Super Contributor
Posts: 275

Issues while using ODS Excel XP in EG

Hello,
I have an issues while creating Excel reports using ExcelXP tagsets. I need to create an Excel workbook with several tabs in it. So, I am reading the excellent literature by some of the staff at SAS. It helped me a lot in building a sample report.

My code is something like this -
ods tagsets.ExcelXp path="" file="" style=customized.style;
ods tagsets.ExcelXp options (sheet_name='Title Page' sheet_interval='none');
Title1 "here goes my first title";
title2 "second title";

multiple data steps and procs...
ods tagsets.ExcelXp options (sheet_name="weekly report" sheet_interval='yes');
proc report (to display the report);
ods tagsets.ExcelXp close;


The issue is that the first tab is not showing up. The second tab (weekly report) shows up with the relevant stylicised report.

Thanks,
Raj.
SAS Super FREQ
Posts: 8,866

Re: Issues while using ODS Excel XP in EG

Hi:
There's really nothing to do with EG that is impacting the TAGSETS.EXCELXP output.

I assume you mean that your "title page" tab is not showing up. What code are you executing to get something -on- the title page???? SAS does not automatically put titles into a sheet -- normally, they go into the header area of the sheet, unless you use the embedded_titles='on' suboption.

But in addition to that -- even if you did HAVE embedded_titles suboption turned on, SAS will only write a TITLE statement if there is a PROC step or DATA step that is writing output. So the reason you have no title on the first sheet is a combination of 2 things:
1) you need to have output -- no output -- no tab
2) when you DO have output to place on the first sheet, then you need embedded_titles='on' as a suboption

Without #1, there's not going to be anyplace for #2 to put the titles.

An alternate method of making an informational title page is to make a small dataset that contains your title info as variables and then use a PROC REPORT (with the NOHEADER option) to print only the "fake" title lines.

Of, if you want more of a table of contents, then look at using the CONTENTS options.

cynthia
Super Contributor
Posts: 275

Re: Issues while using ODS Excel XP in EG

Posted in reply to Cynthia_sas
Thanks Cynthia!!
I think you answered the question. I will try out your suggestions.
Super Contributor
Posts: 275

Re: Issues while using ODS Excel XP in EG

Posted in reply to Cynthia_sas
Hi Cynthia,
I have another question which is related to this topic. So, I am posting it here itself. Currently, I am running the SAS codes in EG which gives me 2 outputs
1. a HTML file with page breaks that shows how the report could look like
2. a XML file that I am creating using the ods tagsets.ExcelXp file=' xml' statement.

Right now the process looks ok. But, eventually, when I move this to production (in Information delivery portal), I only need the XML file to be generated. So, when the users click on the link, would the HTML output also be generated?
If yes, how do I turn it off?
I am using a ods listing off statement. Do I need to use a ODS _all_ close prior to invoking the tagsets?

Thanks,
Raj.
SAS Super FREQ
Posts: 8,866

Re: Issues while using ODS Excel XP in EG

Yes, you are exactly correct. ODS LISTING CLOSE; probably has little impact in EG because the LISTING output (Text output) is not generated by default.

So you need ODS _ALL_ CLOSE; -before- your first invocation of TAGSETS.EXCELXP -- or at the very top of your EG code -- to close the open ODS HTML or ODS SASREPORT statement that EG generally inserts by default. Or, alternately, go to Tools/Options path and turn off the automatic generation of HTML results (although turning it off program by program seems a better approach to me).

When you move this to the IDP, I assume you are also going to generate this output from a Stored Process?? If you want to launch Excel for receiving the output, you may also need to investigate the use of the STPSRV_HEADER function call in order to sent the right content-type header to the browser.

cynthia
Super Contributor
Posts: 275

Re: Issues while using ODS Excel XP in EG

Posted in reply to Cynthia_sas
Hi Cynthia,
Thanks for the note. That does the resolve the issue. I am still struggling to get the report output according to the User Specifications -
I need several tabs in 1 Excel spreadsheet with each tab having 2 tables or more. So, I went down the path of ODS Tagsets.ExcelXp and was able to create tabs with multiple tables. But now manipulating the style elements seem to be problematic. My code looks something like this

I went down Option 2 first but that did not work completely - the parent style got copied down but some of the individual style elements did not work and I am lost digging through the individual style elements. Most of the examples on sas.com and SUGI papers seem to be using Proc Template to create a table but I need to use Proc Report to create the table. Smiley Sad

Now I am using Option 1 but it does not work at all. When I open the Excel output, it says "problems came up while loading the following".

The the final output need to be fairly specific.

***************************************************************;
%let source=/sasdev/.../tagset_codes/;

* Library to store styles and tagsets;
ods path reset;
libname libtmpl "&source";

* Set the ODS search path for styles and tagsets;
ods path libtmpl.tmplmst(update)
sashelp.tmplmst(read);

* Include recent version of the ExcelXP ODS tagset;
%include "/sasdev/.../excel_120/ExcelXp.sas";

***************************************************************;

* Create the required style(s) ;
proc template;
define style Styles.boeing_style;
parent=Styles.Theme;
background=white;
end;
run; quit;
proc template;
define style Sample_Style;
/**** OPTION 1****/

style cellcontents /
background=colors("dark")
foreground=colors("light")
;

/******* OPTION 2******/
define style styles.XLsansPrinter;
parent = styles.sansPrinter;
style header from header /
just = center
font_Face="Courier"
font_weight=bold
foreground=white
background=green;
run;

end;

/***************************************************************/


*Invoke Excel Xp with file name, path;
ods listing close;

ods tagsets.ExcelXP file="sample_report_v4_2.xml" path="&source." style=sample_style;

*Create Title Page;
ods tagsets.ExcelXp options( sheet_name='Title Page'
/* sheet_interval='yes'*/
absolute_column_width='9');
footnote;

Data titles;
tit1="Assessment Completion";
tit2="Week, ";
run;

proc report data=titles noheader;
run;

ods tagsets.ExcelXp options(embedded_titles='yes'
sheet_name='Business Unit HA'
absolute_column_width='9');
Title " Assessment Completion Report by Business Unit";
Title2 "Report Date";
Title3 "Cumulative Period ";
Title4 "Current Week Period ";
proc sort data=sashelp.shoes out=shoes_sorted;
by region subsidiary;
run;
proc means data=shoes_sorted noprint ;
by region subsidiary;
var stores sales inventory returns;
output out=shoes_summary (drop=_freq_ _type_) sum=;
run;quit;
proc report data=shoes_summary headskip wrap style=boeing_style;
col region subsidiary stores sales inventory returns dummy1 dummy2 ;
define region / group ;
define subsidiary / group ;
define store / analysis sum;
define sales / analysis sum;
define inventory / analysis sum;
define returns / analysis sum;
break after region / summarize skip;
rbreak after / summarize skip ;
run;quit;
ods path reset;
ods tagsets.ExcelXp close;
libname libtmpl clear;
SAS Super FREQ
Posts: 8,866

Re: Issues while using ODS Excel XP in EG

Hi:
I guess I don't understand what your style template(s) are doing. I see that you are defining 3 templates -- but you really can only use 1 template at a time in your output. Also, a style TEMPLATE reference goes on the ODS invocation -- not on the PROC REPORT statement.

For example, this is incorrect:
[pre]
proc report data=shoes_summary headskip wrap style=boeing_style;
[/pre]

And you should be seeing a note something like this in the SAS log:
[pre]
NOTE: Unable to find the "BOEING_STYLE" style element. Default style attributes will be used.
[/pre]

Your style template name goes on the ODS invocation statement:
[pre]
ods tagsets.excelxp file='somefile.xls' style=boeing_style;
[/pre]

Given this confusion of usage, I'm not sure what you mean when you say that:
"manipulating the style elements seems to be problematic".

The style elements used in a PROC REPORT table are very simple -- the HEADER element, the DATA element, the BODY element, possibly the NOTECONTENTS element (if you have a LINE statement -- which you don't have). Possbly you could need to change the SystemTitle element or the ByLine style element.

Rather than rush into debugging a style template that doesn't seem to work, can you explain WHAT you need to change?? It might be easier to change the PROC REPORT syntax directly, especially if you need to perform conditional highlighting or want to change an entire row.

What you override with STYLE= syntax in PROC REPORT code are usually style ATTRIBUTES which belong to specific style ELEMENTS. For example, if I have this:
[pre]
ods tagsets.excelxp file='........' style=sasweb;
proc report data=sashelp.shoes nowd
style(header)={background=green};
[/pre]

My code is overriding the HEADER element from the active style template (SASWEB). The particular style ATTRIBUTE that is being overridden is the BACKGROUND attribute (of the HEADER element). In my code snippet, I would be changing the BACKGROUND color and only the BACKGROUND color for ALL the headers on the report to GREEN. You can actually override style ELEMENTS in PROC REPORT override syntax ... but let's show another attribute example.

If I had this:
[pre]
ods tagsets.excelxp file='........' style=sasweb;
proc report data=sashelp.shoes nowd;
column region product sales;
define region / style(header)={background=pink};
[/pre]

Then all the headers would use the color from the SASWEB style except the header for REGION, which would have a pink background.

For an example of highlighting an entire ROW, using CALL DEFINE within PROC REPORT, see this previous forum posting:
http://support.sas.com/forums/thread.jspa?threadID=12444&tstart=0

It's not clear to me how you need to override style elements using a style template or what your style element would be.

I do have a few comments on other aspects of your code. I don't understand what you are doing with your PROC MEANS -- unless you REALLY need PROC MEANS for something else. PROC REPORT can summarize data, especially since you're just getting the SUM statistic for your numeric variables.

And......options like HEADLINE, HEADSKIP, SKIP, FLOW, WRAP, WIDTH and SPACING (to name a few) are LISTING only options that have no impact on ODS destinations like HTML, PDF, RTF and/or TAGSETS.EXCELXP.

Without showing style template code, can you explain how you need to change the style of your PROC REPORT output???

cynthia
Super Contributor
Posts: 275

Re: Issues while using ODS Excel XP in EG

Posted in reply to Cynthia_sas
Thanks Cynthia!!
I think your note cleared a lot of confusion in my head. I guess I was treating the style the incorrect way. I used the sasweb style in the ODS invocation and the style(header) in the proc report and it is working now.

Before I go any further, let me explain what I am trying to achieve. I need the output somewhat this way -
Title1
Smaller title2
Smaller title2

table 1
Table 2

Both Table 1 and Table2 need very specific formats for the headers and the data. I was using SASHELP.shoes because I do not have the real data yet. Since I am playing with SASHELP.shoes please forgive the mix-and-match with field names.

data shoes_summary;
set shoes_summary;
Sales1=Sales;
Inventory1=Inventory;
Returns1=Returns;
run;
proc report data=shoes_summary
style(column)={just=left font_face=arial background=white foreground=black bordercolor=black }
style(header)={just=center font_face=arial foreground=white bordercolor=black background=#2C6700 font_weight=bold};
*column region subsidiary stores sales inventory returns;
column Region Subsidiary ("Eligible Population" Stores )("Cumulative"("Heading1" Sales Sales1)("heading2" Inventory Inventory1 )("heading 3" Returns Returns1) ) ("Current Week"("Subheading1" Sales Sales1 ) ("Subheading2" Inventory Inventory1)("Subheading3" Returns Returns1));
define region / group ;
define subsidiary / group ;
define store / analysis sum;
define dummy1 / noprint;
define sales / analysis sum;
define inventory / analysis sum;
define returns / analysis sum;
break after region / summarize skip;
rbreak after / summarize skip ;
run;quit;

Issue #1 -Now all the 3 rows of header are in the same color. I can change the 3rd row in the define statements. How do I change Row 1?

Issue#2 - Also the color I have used 2C6700 for the background seems to act funny. The HTML output in EG shows the green shade but the XML output shows grey shades.
SAS Super FREQ
Posts: 8,866

Re: Issues while using ODS Excel XP in EG

Hi:
Issue #1 -- not sure what you mean, exactly, What version of SAS are you using??? 9.1.3 or 9.2???

Issue #2 -- this color difference could be a function of the version of Excel. Some Excel versions use a color palette of only 56 colors.

cynthia
Super Contributor
Posts: 275

Re: Issues while using ODS Excel XP in EG

Posted in reply to Cynthia_sas
Hi Cynthia,
I am using SAS 9.1.3. Essentially I need a 3 level header with each row spanning different number of cells and having formats. The 3rd level or row will have all the field names.

Thanks,
Raj.
SAS Super FREQ
Posts: 8,866

Re: Issues while using ODS Excel XP in EG

Hi:
I forgot...to make your titles successively smaller, you would normally do something like this:
[pre]
title1 h=12pt '12 pt Title';
title2 h=10pt '10pt Title';
title3 h=8pt '8pt Title';
[/pre]

I think I posted an example of coloring ACROSS headers for spanned columns not very long ago. You could search for previous forum postings on spanning headers. I will try to find my program and point you in that direction.

Colors for spanning headers in PROC REPORT worked a bit differently in 9.1.3 versus 9.2 -- so you may have to experiment a bit to get it the way you want. The bottom line is that the only way to absolutely guarantee the color or style attributes for a spanning header is to make a "fake" across variable so you can control style attributes in a DEFINE statement.

cynthia
SAS Super FREQ
Posts: 8,866

Re: Issues while using ODS Excel XP in EG

Here's the previous posting with some ACROSS and spanning header code examples:
http://support.sas.com/forums/thread.jspa?messageID=46069돵

cynthia
Super Contributor
Posts: 275

Re: Issues while using ODS Excel XP in EG

Posted in reply to Cynthia_sas
Many thanks Cynthia. I looked at that code and tried it out. It does change the background to what I need. But the flipside is that I now have only 2 levels. Both the Level 1 header are being pushed into the 3rd level as separate columns. Smiley Happy

data shoes_summary;
set shoes_summary;
x='Cumulative';/*Dummy variable - only for placeholder*/
y='Current Week';/*Dummy variable - only for placeholder*/
Sales1=Sales;
Inventory1=Inventory;
Returns1=Returns;
run;
/*
colors
#008000
#339966
*/

proc report data=shoes_summary
style(column)={just=left font_face=arial background=white foreground=black bordercolor=black }
style(header)={just=center font_face=arial foreground=white bordercolor=black background=#339966 font_weight=bold};
*column region subsidiary stores sales inventory returns;
column Region Subsidiary ("Eligible Population" Stores )(x ("Heading1" Sales Sales1)("heading2" Inventory Inventory1 )("heading 3" Returns Returns1) ) (y ("Subheading1" Sales Sales1 ) ("Subheading2" Inventory Inventory1)("Subheading3" Returns Returns1));
define region / group ;
define subsidiary / group ;
define x / across ' ' style(header)=[foreground=black background=white font_weight=bold];
define store / analysis sum;
define sales / analysis sum;
define inventory / analysis sum;
define returns / analysis sum;
define y / across ' ' style(header)=[foreground=black background=white font_weight=bold];
break after region / summarize skip;
rbreak after / summarize skip ;
run;quit;

The X and Y are the dummy variables.

Also, thanks for the tip on the title. I have all the titles being centered in the xml file. Is there a way to justify some of them to left and wrap them? I tried 3 methods but they did not work-
1. j=l 2.justify=left and 3. html tags which i do not want to post here I tried this -
column Region Subsidiary x,("Eligible Population" Stores )y,(("Heading1" Sales Sales1)("heading2" Inventory Inventory1 )("heading 3" Returns Returns1) ) z,( ("Subheading1" Sales Sales1 ) ("Subheading2" Inventory Inventory1)("Subheading3" Returns Returns1));
It is better - I have the 2 spanning headers in the right format but rest of the row is in the report background Smiley Sad


Message was edited by: saspert
SAS Super FREQ
Posts: 8,866

Re: Issues while using ODS Excel XP in EG

Hi:
I don't have an issue with using j=c or j=r or j=l in my TITLE statements. See the code example below.

Also note, that there is an ENORMOUS difference between using ACROSS items with and without a comma in the COLUMN statement. See the difference between report #1 and report #2 in my program's output. In my program, when I used "fake" ACROSS items in the other program I posted, I also used a comma in the COLUMN statement:
[pre]
column grp x,name y,(sex age) z,(height weight);
[/pre]

I have not blanked out ANY of the values of the ACROSS items headers, so you can see exactly how the spanning works.

cynthia
[pre]
ods listing;
ods tagsets.ExcelXp file='title_ques.xls' style=sasweb
options(embedded_titles='yes' sheet_name='Business Unit HA' absolute_column_width='9');
Title j=l "(left) 1) ACROSS without a COMMA";
Title2 j=c "(center) Report Date";
Title3 j=c "(center) Cumulative Period ";
Title4 j=r "(right) Current Week Period ";

proc report data=sashelp.shoes nowd;
where product in ('Boot', 'Sandal', 'Slipper');
column region product sales inventory returns;
define region / group;
define product / across;
define sales/ sum;
define inventory / sum;
define returns / sum;
run;

Title j=l "(left) 2) ACROSS -WITH- a COMMA";
Title2 j=c "(center) Report Date";
Title3 j=c "(center) Cumulative Period ";
Title4 j=r "(right) Current Week Period ";

proc report data=sash elp.shoes nowd;
where product in ('Boot', 'Sandal', 'Slipper');
column region product, ( sales inventory returns);
define region / group;
define product / across;
define sales/ sum;
define inventory / sum;
define returns / sum;
run;
ods _all_ close;
[/pre]
Super Contributor
Posts: 275

Re: Issues while using ODS Excel XP in EG

Posted in reply to Cynthia_sas
Hi Cynthia,
Many thanks for the tip. It cleared a lot of confusion. But still, there are a few *minor* (haha..) formatting details I still need -
1. The justification works - I had to download the latest tagsets. The old ones were from 2007. Smiley Happy

2. I got my 3 level header using your example with comma. I was able to get the Product cell (in your example) to have black foreground and white background (using the define statement). But, the issue is with the cell before the "product" cell. It still has the default header style (or the sasweb style in your case).
- I need the entire row to have black foreground and white background.

3. The option for embed_titles_once does not seem to work. I have the same set of titles being repeated twice. I think I will ask the tech support regarding this.

4. I am still working on formatting the group breaks. I will post a note if I am stuck with this.

Many many thanks,
Raj.
Ask a Question
Discussion stats
  • 15 replies
  • 659 views
  • 0 likes
  • 2 in conversation