DATA Step, Macro, Functions and more

How to clear the fill of the whole Excel workbook?

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

How to clear the fill of the whole Excel workbook?

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!


Accepted Solutions
Solution
‎11-11-2014 07:53 AM
Respected Advisor
Posts: 3,777

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

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 ssSmiley Tongueattern="Solid" />','i')
     
then _infile_=tranwrd(_infile_,"Solid","None");
   put _infile_;
  
run;

View solution in original post


All Replies
Super User
Posts: 17,818

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

This currently isn't a feature available in Tagsets.ExcelXP Smiley Sad

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.

Contributor
Posts: 53

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

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

Super User
Super User
Posts: 7,401

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

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 ssSmiley Tongueattern="Solid" />

<Protection ssSmiley Tonguerotected="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 ssSmiley Tongueattern="Solid" />') > 0 then buffer=tranwrd(buffer,"Solid","None");

  put buffer;

run;

Solution
‎11-11-2014 07:53 AM
Respected Advisor
Posts: 3,777

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

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 ssSmiley Tongueattern="Solid" />','i')
     
then _infile_=tranwrd(_infile_,"Solid","None");
   put _infile_;
  
run;
Super User
Super User
Posts: 7,401

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

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.

Respected Advisor
Posts: 3,777

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 347 views
  • 3 likes
  • 4 in conversation