The SAS Output Delivery System and reporting techniques

Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

Reply
Contributor
Posts: 31

Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

I need to create a tabulate output that has true blank values when opened with Excel.  The final project involves creating graphs with gaps for missing values (not zero) and excel needs the cells to be truly blank.  They should be able to return a TRUE value when tested with =isblank().

 

ODS HTML created a true blank, but I need both the multi-sheet and formatting options available with tagsets/excel.

 

I have tried several option missing='' type solutions, I have tried both ODS, I have tried XML,XLS, and XLSX.  I have tried tabulate and report.

 

What can I do?

Super User
Posts: 11,793

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

Provide some example data in the form of a data step and the proc tabulate code and how you are attempting to send things to Excel.

 

If the issue is the . appearing for missing in Proc tabulate output instead of blank you could use either the MISSTEXT=' ' as a table option or change the default missing character before running the tabulate code with an options statement:

 

options missing=' ';

Proc tabulate ....

 

You might consider using the SAS graphic capabilities and skipping any weird changes in data required to get Excel to graph something.

 

Contributor
Posts: 31

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

Here's some very simple code.

 

data test;
input month sales;
datalines;
1 10
2 20
3 30
4 .
5 50
6 60
;
run;
option missing='';
%let myWorkLocation = %sysfunc(pathname(work));

ods tagsets.excelxp
file= "&myWorkLocation./test.xml";

proc tabulate data = test missing;
class month;
var sales;
table (month='')*(sales='' *sum='');
run;
ods tagsets.excelxp close;

 

 

As long as the excel book returns FALSE for =isblank(D2), this won't work.

 

SAS' graphing capabilities are fine for analysis, they are terrible for presentation. 

Super User
Posts: 20,730

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP


ProcWes wrote:

 

 

SAS' graphing capabilities are fine for analysis, they are terrible for presentation. 


GTL is pretty damn good IMO, especially when it comes to automating graphs. SAS\GRAPH isn't. The styles leave a lot to be desired..but the ability to basically customize everything is available via GTL.

 

I've found that the SAS missing (.) doesn't cause issues with Excel (2013+), assuming you're using SAS to populate data that feeds into a graph automatically. 

 

At the same time I probably wouldn't expect a well structured table from PROC TABULATE that I'd be using to populate a graph.

 

Contributor
Posts: 31

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

Can you modify the code I provided to an output that will pass the isblank() test?

Super User
Super User
Posts: 7,255

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

You will probably have better luck creating an empty cell if you use PROC EXPORT or the XLSX libname engine.  Now that PROC TABULATE code is going to create a table that has the MONTH number as the column heading.

image.png

That format is probably going to be hard to produce as a dataset. But why not just re-code the XLSX file to expect the data in a more normal format. Like that produced using PROC SUMMARY?

 

image.png

 

Contributor
Posts: 31

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

I have 8 tabs of 3 cross tabs. I can't see this being the right answer.
Super User
Super User
Posts: 7,255

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

[ Edited ]

ProcWes wrote:
I have 8 tabs of 3 cross tabs. I can't see this being the right answer.

I agree it would be nice if ODS EXCEL and/or PROC TRABULATE could be updated to produce properly empty cells for missing values. But it looks like that is not going to be possible quickly and it currently already IS possible to produce an Excel tab with empty cells using the XLSX engine then you should be able to solve your immediate problem.

 

image.png

 

Note that if the goal of the SAS program is to generate data that is then going to be graphed by Excel then using a data manipulation procedure like PROC SUMMARY or PROC FREQ to calculate the numbers you need is probably more appropriate than using a reporting tool like PROC TABULATE or PROC REPORT. 

Contributor
Posts: 31

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

[ Edited ]

Here's one of the cells from looking at the XML output using Tagsets.ExcelXP

 

<Cell ss:StyleID="data__r1" ss:Index="16"><Data ss:Type="String"></Data></Cell>

 

That same cell using ODS HTML:

<td class="r b data" #,##0_);[Red](#,##0);_("-"??_)>&nbsp;</td>

 

Here is an example on how the tagset XML is changing the cell format when it sees missing:

 

<Cell ss:StyleID="data__r1" ss:Index="15"><Data ss:Type="Number">89987</Data></Cell>
<Cell ss:StyleID="data__r1" ss:Index="16"><Data ss:Type="String"></Data></Cell> 

 

You can see it changes from number to string.

Super User
Posts: 20,730

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

My point was Excel graphs don't necessarily care about that if you design the graphs/VBA code. I don't know how to get missing, but I've exported data to Excel and use VB scripts to automate the graphs or have the graphs in place already and the data 'updates' the graphs. Using PROC EXPORT it shows as missing, likely character but doesn't cause issues with my processes.

Overarching point - it may be easier to fix your Excel process than to get a blank.
Contributor
Posts: 31

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

The =NA() formula in Excel returns #N/A in the cell, which Excel won't graph.  If I could get that value to somehow populate the cells, that'd work.

Super User
Posts: 20,730

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP


ProcWes wrote:

The =NA() formula in Excel returns #N/A in the cell, which Excel won't graph.  If I could get that value to somehow populate the cells, that'd work.


What does that mean?

Contributor
Posts: 31

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

What part was unclear?
Super User
Posts: 20,730

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

I don't know what you were trying to say...what do you need to see in the cell instead of NA?

 

Would 0 work? 

 

Have you tried the misstext on PROC TABULATE? I set it to 0 below, but you could also try a blank space and see if that works for you.

 

proc tabulate data = test missing;
class month;
var sales;
table (month='')*(sales='' *sum='') /misstext='0';
run;
Contributor
Posts: 31

Re: Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

I need nothing in the cell. Nothing at all: not zero, NA, space.... nothing.

It has to be able to return TRUE for =isblank(). With ODS HTML, this was the case, but something is different about the output, as seen above with the XML examples.
Ask a Question
Discussion stats
  • 35 replies
  • 539 views
  • 3 likes
  • 5 in conversation