BookmarkSubscribeRSS Feed
polingjw
Quartz | Level 8
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.
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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
Quartz | Level 8
Thanks Cynthia. As always, your detailed explanations have proven to be very helpful.
Ksharp
Super User
As far as I know ods rft can do it.
polingjw
Quartz | Level 8

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" ss:Parent="data"><Alignment ss:WrapText="1" ss:Horizontal="Left"/><Font ss:StrikeThrough="1"/>

</Style>

<Style ss:ID="StrikeThrough_R" ss:Parent="data"><Alignment ss:WrapText="1" ss:Horizontal="Right"/><Font ss:StrikeThrough="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;

Cynthia_sas
SAS Super FREQ

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

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1843 views
  • 0 likes
  • 3 in conversation