Hi!
I need to output a table using ODS TAGSETS.EXCELXP. I'm using the parent style minimal, however the "no fill" option is only applied in the table, but not outside the table. The background of the workbook is just all white outside the table. May I know how can the "no fill" option be applied to the whole Excel workbook when the SAS table is output?
Using the proc export dbms = excel is not an option since this prevents the export of SAS formats.
Thank you!
Your program seems to work OK for this test data but your choice of options is a bit disconcerting; DSD and MISSOVER should not be used and the LRECL should be the same for FILE and INFILE. You can modify the BUFFER directly it is called _INFILE_ by default (there is an option to change the name).
This currently isn't a feature available in Tagsets.ExcelXP
I believe ODS HTML can generate a file that will export SAS formats and maintain the no fill option. Still not a native Excel file but an option.
I'm just thinking --- can this be done thru a VB script?
Hi,
Well, you can just post-processes the output XML. If you right click on the output file and open in Notepad you will see it is just a text file in XML format which Excel interprets. You can manipulate this file after export as per any text file. To get rid of the solid white background look for:
<Style ss:ID="_body">
<Interior ss:Pattern="Solid" />
<Protection ss:Protected="1" />
Its the solid part which causes the issue. So do:
ods tagsets.excelxp file="s:\temp\rob\tt.xls" style=minimal;
proc report data=sashelp.class nowd;
columns name sex age height weight;
run;
ods tagsets.excelxp close;
data _null_;
infile "s:\temp\rob\tt.xls" lrecl=32767 dsd missover;
file "s:\temp\rob\tx.xls";
length buffer $200.;
input buffer $;
if index(buffer,'<Interior ss:Pattern="Solid" />') > 0 then buffer=tranwrd(buffer,"Solid","None");
put buffer;
run;
Your program seems to work OK for this test data but your choice of options is a bit disconcerting; DSD and MISSOVER should not be used and the LRECL should be the same for FILE and INFILE. You can modify the BUFFER directly it is called _INFILE_ by default (there is an option to change the name).
Cut and paste from another program which uses different data. Didn't even think about the reading, main point I was trying to make was to just text replace that XML option.
Yes I understand your point but I don't expect the OP is an expert at INFILE/FILE and the rest.
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: