The SAS Output Delivery System and reporting techniques

Strikethrough rows in the tagset.excelXP destination

Reply
Regular Contributor
Posts: 171

Strikethrough rows in the tagset.excelXP destination

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.
SAS Super FREQ
Posts: 8,743

Re: Strikethrough rows in the tagset.excelXP destination

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 ssSmiley FrustratedtyleID="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" ssSmiley Frustratedize="11" ss:Color="#000000"
ssSmiley FrustratedtrikeThrough="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 ssSmiley FrustratedtrikeThrough attribute is used and written to the XML and set as you want. (By default, no ssSmiley FrustratedtrikeThrough 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 ssSmiley FrustratedtyleID reference; AND,
2) have the <STYLE> section generated include the ssSmiley FrustratedtyleID 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 ssSmiley FrustratedtrikeThrough attribute and alter the <ROW> tag

cynthia
Regular Contributor
Posts: 171

Re: Strikethrough rows in the tagset.excelXP destination

Thanks Cynthia. As always, your detailed explanations have proven to be very helpful.
Super User
Posts: 9,682

Re: Strikethrough rows in the tagset.excelXP destination

As far as I know ods rft can do it.
Regular Contributor
Posts: 171

Re: Strikethrough rows in the tagset.excelXP destination

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 ss:ID="StrikeThrough_L" ssSmiley Tonguearent="data"><Alignment ss:WrapText="1" ss:Horizontal="Left"/><Font ssSmiley FrustratedtrikeThrough="1"/>

</Style>

<Style ss:ID="StrikeThrough_R" ssSmiley Tonguearent="data"><Alignment ss:WrapText="1" ss:Horizontal="Right"/><Font ssSmiley FrustratedtrikeThrough="1"/>

</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;

SAS Super FREQ
Posts: 8,743

Strikethrough rows in the tagset.excelXP destination

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

cynthia

Ask a Question
Discussion stats
  • 5 replies
  • 560 views
  • 0 likes
  • 3 in conversation