topic Re: How to clear the fill of the whole Excel workbook? in SAS Programming

How to clear the fill of the whole Excel workbook?

angeliquec — Tue, 11 Nov 2014 03:08:49 GMT

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!

Re: How to clear the fill of the whole Excel workbook?

Reeza — Tue, 11 Nov 2014 04:54:17 GMT

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.

Re: How to clear the fill of the whole Excel workbook?

angeliquec — Tue, 11 Nov 2014 05:55:27 GMT

I'm just thinking --- can this be done thru a VB script?

Re: How to clear the fill of the whole Excel workbook?

RW9 — Tue, 11 Nov 2014 12:13:08 GMT

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:

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;

Re: How to clear the fill of the whole Excel workbook?

data_null__ — Tue, 11 Nov 2014 12:53:36 GMT

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).

ods tagsets.excelxp file="~/tt.xls" style=minimal;
proc report data=sashelp.class(obs=2) nowd; 
   columns _all_;
   run; 
ods tagsets.excelxp close; 
data _null_; 
   infile "~/tt.xls" lrecl=1024; 
   file   "~/tx.xls" lrecl=1024; 
   input; 
   if find(_infile_,'<Interior ss:Pattern="Solid" />','i') 
      then _infile_=tranwrd(_infile_,"Solid","None");
   put _infile_; 
   run; 

Re: How to clear the fill of the whole Excel workbook?

RW9 — Tue, 11 Nov 2014 13:38:15 GMT

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.

Re: How to clear the fill of the whole Excel workbook?

data_null__ — Tue, 11 Nov 2014 14:39:36 GMT

Yes I understand your point but I don't expect the OP is an expert at INFILE/FILE and the rest.