BookmarkSubscribeRSS Feed
ProcWes
Quartz | Level 8

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?

35 REPLIES 35
ballardw
Super User

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.

 

ProcWes
Quartz | Level 8

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. 

Reeza
Super User

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

 

ProcWes
Quartz | Level 8

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

Tom
Super User Tom
Super User

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

 

ProcWes
Quartz | Level 8
I have 8 tabs of 3 cross tabs. I can't see this being the right answer.
Tom
Super User Tom
Super User

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

ProcWes
Quartz | Level 8

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.

Reeza
Super User
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.
ProcWes
Quartz | Level 8

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.

Reeza
Super User

@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?

ProcWes
Quartz | Level 8
What part was unclear?
Reeza
Super User

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;
ProcWes
Quartz | Level 8
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 35 replies
  • 4531 views
  • 3 likes
  • 5 in conversation