Strikethrough rows in the tagset.excelXP destination

polingjw · Posted 05-26-2011 11:21 AM

When writing to the tagsets.excelXP destination, is there a way to strikethrough a set of rows in the resulting spreadsheet? What I want to do is analogous to the following code that works in the PDF destination when running SAS 9.2: [pre]
ods listing close;
ods pdf;
proc report data=sashelp.class nowindows;
column name sex age height weight;
compute name;
if name in ('Barbara' 'Jeffrey' 'John') then call define(_row_, 'style', 'style=[textdecoration=line_through]');
endcomp;
run;
ods pdf close;
ods listing; [/pre]
Thanks in advance for any advice.

Cynthia_sas · Posted 05-26-2011 03:22 PM

Hi:
Let's take SAS out of the picture and go directly to Excel.
1) Open Excel and type some values in a few cells;
2) Use the STRIKETHROUGH feature of the font pull-down menu to change the font in a cell;
3) Manually Save the Excel file as Excel 2003 XML;
4) Open the XML file with Notepad;
5) Look for the text with the Striketrhough font attribute set ( in my file, the row was a row for Carla) and I see in Notepad that the row was styled with the S62 style selector;
This is the XML created by Excel with SAS out of the picture:
[pre]
<Row ss:StyleID="s62">
<Cell><Data ss:Type="String">Carla</Data></Cell>
<Cell><Data ss:Type="Number">333</Data></Cell>
</Row>
[/pre]

6) Look in the style section of the XML for the S62 style selector and find this:
[pre]
<Style ss:ID="s62">
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
ss:StrikeThrough="1"/>
</Style>
[/pre]

So, with SAS out of the picture, we see that it is possible, in Excel to use the StrikeThrough font attribute. However, that is only half of the problem. Now we know what the XML looks like when you use Excel by itself.

Now the question becomes -- is it possible, with SAS to change the style section being written by TAGSETS.EXCELXP so that the ss:StrikeThrough attribute is used and written to the XML and set as you want. (By default, no ss:StrikeThrough attribute is ever written by ODS TAGSETS.EXCELXP.)

This new question is a question for Tech Support, in my opinion. That is my recommendation, because you need to
1) have the <ROW> tag changed for your row of interest to use the ss:StyleID reference; AND,
2) have the <STYLE> section generated include the ss:StyleID that you reference in #1;

And I don't actually know whether you can accomplish what you want to do with a STYLE= override (doubtful, in my opinion); or with a STYLE template or with a TAGSET template change -- or with some combination of all the techniques -- OR
possibly post-process the XML file to add the ss:StrikeThrough attribute and alter the <ROW> tag

cynthia

polingjw · Posted 05-31-2011 05:57 AM

Thanks Cynthia. As always, your detailed explanations have proven to be very helpful.

Ksharp · Posted 06-02-2011 03:18 AM

As far as I know ods rft can do it.

polingjw · Posted 07-27-2011 07:52 PM

Cynthia,

Sorry about my very belated response to this thread. I was able to do some DATA step post-processing on the XML code to modify the style elements that you pointed out. I just wanted to says thanks again for all the help and to offer a potential solution to this problem for anyone who might be interested.

ods listing close;

ods tagsets.excelxp file='class.xls' style=sansprinter;

proc report data=sashelp.class nowindows;

column name sex age height weight;

run;

ods tagsets.excelxp close;

ods listing;

/*** Store the new XML style definitions for StrikeThrough cells in a data set. One style definition is created for left-aligned data

and one style definition is created for right-aligned data. ***/

data strikethrough_styles;

length line $ 200;

infile datalines truncover;

input line &;

datalines;

</Style>

</Style>

;;;

run;

/*** Construct a macro variable containing the row numbers to strike through in the spreadsheet. The number one is added

to the _n_ variable to account for the row in the spreadsheet which will contain variable names. ***/

%let RowNums = ;

data _null_;

set sashelp.class;

if name in ('Barbara' 'Jeffrey' 'John') then call symput("RowNums", catx(' ', resolve('&RowNums'), _n_+1));

run;

data xml_code (keep=line);

/*** Read the XML code from the spreadsheet ***/

length line $ 200;

infile 'class.xls' truncover;

input line &;

/*** The line </Styles> denotes the end of the style definitions. Once this line is encountered, add the two new style

definitions we defined previously. ***/

if strip(line) = '</Styles>' then do;

do until(eof);

set strikethrough_styles end=eof;

output;

end;

line='</Styles>';

output xml_code;

end;

else do;

/*** Lines that begin with <Row denote a new row. Increment the row number counter by one. ***/

if substr(line, 1, 4) = '<Row' then RowNum + 1;

/*** Apply the a new strikethrough style if a cell is being defined in one of the previously established row numbers.

Numbers will be right-aligned and strings will be left-aligned. The names of the new style definitions will overwrite

the values which were previously defined in the StyleID attribute. ***/

if substr(line, 1, 5) = '<Cell' and RowNum in (&RowNums) then do;

if find(line, 'Type="Number"') then line=tranwrd(line, scan(substr(line, find(line, 'StyleID=')+8), 1, '"'), "StrikeThrough_R");

else if find(line, 'Type="String"') then line=tranwrd(line, scan(substr(line, find(line, 'StyleID=')+8), 1, '"'), "StrikeThrough_L");

end;

output xml_code;

end;

run;

/*** Recreate the spreadsheet using the modified XML code. ***/

data _null_;

file 'class.xls';

set xml_code;

put line;

run;

Cynthia_sas · Posted 07-28-2011 12:23 PM

Glad you got it working. That's the approach I would have used.

cynthia