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;
Glad you got it working. That's the approach I would have used.
cynthia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: