topic Re: excelxp: create yellow cell below proc report in ODS and Base Reporting

excelxp: create yellow cell below proc report

gzr2mz39 — Thu, 04 Oct 2012 17:10:28 GMT

I would like to create a yellow cell below my proc report output.

I'm using ods tagsets.excelxp to create the excel file (which is initially outputted as an xml file).

If, for example, my proc report table ends at row 10 I would like to add a cell with a yellow background in row 12 column A.
Any ideas?

Re: excelxp: create yellow cell below proc report

ballardw — Thu, 04 Oct 2012 20:12:53 GMT

Untested but my first thought would be an ODS tagsets.excelxp Text= with a style to provide a yellow background to text. You probably would need to provide a text literal to get it to display. You don't say if you want text or not. If not a NULL charcter (ASCII 255) might work.

From some experience with other ODS output destinations this might appear on the third row after your table output as the ODS templates seem to have some element that isn't exposed to our control that outputs 2 blank lines regardless of destination.

Re: excelxp: create yellow cell below proc report

Cynthia_sas — Fri, 05 Oct 2012 00:59:02 GMT

Hi:

I can't find it now, but I remember something about ODS TEXT= and TAGSETS.EXCELXP. I am not sure that it works in all circumstances. It might be something to test out. For example, in the code below, run in SAS 9.3, and opened in Excel 2010, the output from ODS TEXT= does not appear in my file, when the XML file is opened in Excel. I used TAGSETS.EXCELXP version v1.122 and also tested in version 1.127 -- and ODS TEXT= output did not appear in either output file.

In the output created below, however, note that any text written with a LINE statement spans the whole table. And, if ODS TEXT did work, then it, too would span the entire table -- the text, like the LINE output would not settle just in COLUMN A.

SAS does not have the concept of "column A" or "row 10" -- TAGSETS.EXCELXP makes an XML table from the data you send to PROC REPORT. PROC REPORT makes the report. If you have a BREAK or RBREAK statement, then summary lines get added. The summary information is an entire "row"...of summarized columns. As I show, you can "touch" one of the cells in the summary row with a COMPUTE block, but you only get 1 report row for each BREAK or RBREAK statement. And, if you use a LINE statement, then your "LINE area" spans the whole table, not just one cell.

cynthia

ODS TAGSETS.EXCELXP file='c:\temp\makeyellow.xml' style=sasweb

options(doc='Help');

title;

ods listing close;

proc report data=sashelp.class nowd;

column name age sex height weight;

rbreak after / summarize;

compute after /style={background=pink just=l};

call define('name','style','style={background=yellow}');

line 'This is a line';

endcomp;

run;

ods text='some text';

ODS TAGSETS.EXCELXP CLOSE;