topic Re: Output Blank Tabulate in ODS and Base Reporting

Output Blank Tabulate

wcp_fnfg — Mon, 08 Sep 2014 20:50:22 GMT

It seems that if there are no records in a tabulate that it doesn't output anything. This is in contrast to the scenario where I have preloadfmt\printmiss and can get rows\columns even for missing values. Is there a way around this?

In this example, if I set where letters = 'D' then I get a 3 column table, but with letters = 'E', I get nothing. I'd like to still get 3 columns with this table. Any thoughts? Classdata with a dummy table hasn't worked for me either.

data dummy;

do numbers = 1,2,3,4;

do letters = 'A','B','C','D';

output;

end;

run;

proc format;

value $L

'A'='A'

'B'='B'

other = 'X';

run;

proc tabulate data=dummy missing;

where letters = 'E';

class letters/preloadfmt;

var numbers;

tables (letters='')*(

numbers * sum='' *f=comma24.0 )

/printmiss;

format letters $l.;

run;

Re: Output Blank Tabulate

stat_sas — Mon, 08 Sep 2014 21:02:09 GMT

You have no observation for letters='E' in your dataset. Where clause in proc tabulate will return nothing.

Re: Output Blank Tabulate

Cynthia_sas — Tue, 09 Sep 2014 01:44:18 GMT

HI:

I agree -- with E in the data, the results of your code do produce empty cells for A and B. But without E in the table, your WHERE has nothing to select.

Cynthia

data dummy;

do numbers = 1,2,3,4;

do letters = 'A','B','C','D','E';

output;

end;

run;

proc format;

value $L

'A'='A'

'B'='B'

other = 'X';

run;

ods html file='c:\temp\show_preloafmt.html';

proc tabulate data=dummy missing;

** Without PRELOADFMT or format for LETTERS;

where letters = 'E';

title '1) Without PRELOADFMT';

class letters;

var numbers;

tables (letters='')*(

numbers * sum='' *f=comma24.0 )

/printmiss;

run;

proc tabulate data=dummy missing;

title '2) WITH PRELOADFMT for LETTERS';

where letters = 'E';

class letters/preloadfmt;

var numbers;

tables (letters='')*(

numbers * sum='' *f=comma24.0 )

/printmiss;

format letters $L.;

run;

ods html close;

Re: Output Blank Tabulate

wcp_fnfg — Tue, 09 Sep 2014 12:39:31 GMT

Neither of you are listening to the problem. The problem is that I want the table to exist, even if E doesn't exist in the data.

Re: Output Blank Tabulate

wcp_fnfg — Tue, 09 Sep 2014 13:14:42 GMT

I can make a dummy set to merge into the real data for my output, but I still have to deal with this:

WARNING: A logical page containing only missing values has been deleted from the output.

Re: Output Blank Tabulate

Cynthia_sas — Wed, 10 Sep 2014 04:08:21 GMT

Hi:

I am sorry you feel I wasn't listening. However in your post, you said:

"In this example, if I set where letters = 'D' then I get a 3 column table, but with letters = 'E', I get nothing. I'd like to still get 3 columns with this table. Any thoughts? Classdata with a dummy table hasn't worked for me either."

Apparently, I did not sufficiently understand what you meant by "I'd like to still get 3 columns with this table." To me, "This table" meant that you wanted to get a 3 column table. You need data for that. But silly me, like silly TABULATE can't imagine a table where ALL the class values in the columns are missing. If your data has NO values for A, NO values for B and NO values for C, D or E, then there isn't really a way for TABULATE to do anything -- as you can tell from the WARNING when you try to fake out some data to force the values, TABULATE is not happy about a table where ALL the values are missing.

It seems like you have come up with a solution of sorts, by merging in some missing data for every class variable. The WARNING is not an ERROR, so you must be getting an empty table. Is the only issue the WARNING?

cynthia

Re: Output Blank Tabulate

wcp_fnfg — Wed, 10 Sep 2014 12:57:28 GMT

As much as I enjoy the snark, let's discuss what the issue is and what solution I've had to use.

If we use [where letters = 'A'] (or B or C or D) then we get a table with three columns, no matter which letter we pick in the data:

A B Other

X . .

However, with [where letters = 'E'], we get no output. What I was looking for was to still get the 3 column table with everything missing.

A B Other

. . .

What I have done as a workaround is create a dummy table for each of my where\class values and set numbers = 0 for each of these dummy values. Then when I get to the E table, I get a table with three columns output, zeros as each value.

A B Other

0 0 0

This works enough for my purposes, though if someday another letter (F? J? Who knows?) enters the fold, I'd have to add it to the list in my dummy data.

data dummy;

do letters = 'A','B','C','D','E';

output;

end;

run;

So silly.

Re: Output Blank Tabulate

Cynthia_sas — Wed, 10 Sep 2014 14:18:46 GMT

Hi:

Yes, that is what you will have to do (add the new letter(s) to your dummy table). Honestly, I wasn't aiming at snarky -- TABULATE just isn't happy about showing you an "all empty" table. And, equally, honestly, I can't imagine a usage scenario where I would need to generate an entirely empty table. When I was documenting tables and reports for statisticians and lawyers, my requirement/convention was to make dummy data with all '9' or all '0' for the values and we would use that as the model. There was always a footnote or note that said "Sample table displays values for illustration purposes only" if the numbers in the cells were anything other than 0....and sometimes if the numbers were 0. Usually for statisticians, we didn't need the note. Usually for lawyers we always needed the note that it was fake data (especially if we were sending it to the attorney on the other side of the case.)

cynthia

Re: Output Blank Tabulate

wcp_fnfg — Wed, 10 Sep 2014 14:21:22 GMT

The reason I needed the empty table was for the output so that Row X is always Row X and not X-5 or whatever.

Re: Output Blank Tabulate

ballardw — Wed, 10 Sep 2014 14:55:39 GMT

I don't understand, your example data and tabulate code only generates one row. So how do you get row X-5???

Re: Output Blank Tabulate

wcp_fnfg — Wed, 10 Sep 2014 14:58:53 GMT

X-5 was my "real data" example.

Here, it'd be X-2ish. That is one row for the values, one row for the A\B\Other header and then some number of rows for the page spacing in the excel book (rows between tables).

Re: Output Blank Tabulate

Cynthia_sas — Wed, 10 Sep 2014 15:02:02 GMT

Hi: I agree with ballardw -- you do not have an row dimension in your table, only columns, so I don't see how you get any rows at all.

But for an "empty" table, another approach is to make the foreground color and background color for the "data" cells the same (in my code -- both are changed to white). This effectively makes the numbers in the cell disappear. This will work for ODS destinations that support style.

Cynthia

ods rtf file='c:\temp\show_empty.rtf';

ods html file='c:\temp\show_empty.html';

ods pdf file='c:\temp\show_empty.pdf';

proc tabulate data=dummy missing

style={color=white background=white};

title '3) use white for background and foreground color of numbers in cell';

class letters/preloadfmt;

var numbers;

tables (letters='')*(

numbers * sum='' *f=comma24.0 )

/printmiss;

format letters $L.;

run;

ods _all_ close;

Re: Output Blank Tabulate

Cynthia_sas — Wed, 10 Sep 2014 15:03:44 GMT

Hi:

This is where it might be useful to see ALL your code. Are you using ODS to get your output into an Excel workbook or are you using the LIBNAME engine/PROC EXPORT? My previous example, to change the colors in the output data cells will work for ODS output, but not for PROC EXPORT or the LIBNAME engine.

cynthia

Re: Output Blank Tabulate

ballardw — Wed, 10 Sep 2014 15:06:12 GMT

If I really need to control rows I would look at pre-summarizing the data and then using File Print ODS and put _ods_. I think that might give you the ability to control the line/row positions by inserting blank rows into your data at appropriate locations.

Metatheorem: The more control you want over your output the more complex the code. I don't think a single tabulate step will get you where you want due to the reasons Cynthia mentioned.

Re: Output Blank Tabulate

wcp_fnfg — Wed, 10 Sep 2014 15:10:49 GMT

If letters = 'D', there the table is three rows tall as written in the original tabulate

A B X

numbers numbers numbers

. . 10

To your latest reply: I'm using tagsets.excelxp. There's a dozen tabs with 3 tables each, each created as the result of a different where. These tabs are then linked to an excel book to create a dashboard because SAS can't create the appropriate layout that was requested. As a result, I'm relying on the tables to be in the same location on each tab. So if table 1 populates and takes up rows 1-6, and table 2 is supposed to be rows 8-12, but doesn't populate or output then table 3 is going to be at row 8 instead of row 14.

For me, this point is moot now because I've created the solution using the dummy data, I was just hoping there was an option or something that would tell sas or tabulate to create the table, even if there is no data for it. The solution ended up being to make sure that I had a value for each Where possibility and it had a value for the summary variable of zero, rather than missing. Tabulate has no problem outputting a table of zeros.

Re: Output Blank Tabulate

PGStats — Wed, 10 Sep 2014 15:11:59 GMT

You're right, there seems to be no way to get TABULATE to produce an empty table, even when it would make perfect sense. Empty tables are always dropped. Consider :

data test;

page = 1;

cat = "A"; line=1; x=1; output;

cat = "B"; line=2; x=1; output;

run;

proc format;

value $cat

"A" = "A"

"B" = "B"

"C" = "C"

"D" = "D"

"E" = "E";

value line

1 = 1

2 = 2

3 = 3

4 = 4;

value page

1 = 1

2 = 2;

run;

proc tabulate data=test missing format=2.;

format cat $cat. line line. page page.;

class cat line page / preloadfmt;

var x;

table page, line, cat*x=""*mean="" / printmiss;

run;

/* Another approach that would make sense */

data myClassData;

do page = 1, 2;

do line = 1 to 4;

do cat = "A","B","C","D","E";

output;

end;

run;

proc tabulate data=test classdata=myClassData exclusive format=2.;

class cat line page;

var x;

table page, line, cat*x=""*mean="";

run;

With both approaches, the empty table on page 2 is dropped.

Re: Output Blank Tabulate

wcp_fnfg — Wed, 10 Sep 2014 15:14:20 GMT

PGStats: you're so close to what works though! Just need the x=0 line and tabulate will play.

data myClassData;

do page = 1, 2;

do line = 1 to 4;

do cat = "A","B","C","D","E";

x= 0;

output;

end;

run;

Re: Output Blank Tabulate

PGStats — Wed, 10 Sep 2014 15:19:28 GMT

x = 0; would be ignored in the CLASSDATA dataset. The CLASSDATA dataset only indicates to the procedure what class combinations you want in your output. I did include page=2 in there, so I explicitly request a page 2 table. But tabulate decides to drop it anyway! Odd...

Re: Output Blank Tabulate

wcp_fnfg — Wed, 10 Sep 2014 15:22:52 GMT

Ah sorry, I misread what you were doing there.

Here's the\A solution for the original problem, should anyone ever come here looking for how to get it.

data real;

do numbers = 1,2,3,4;

do letters = 'A','B','C','D';

output;

end;

run;

proc format;

value $L

'A'='A'

'B'='B'

other = 'X';

run;

data dummy;

do letters = 'A','B','C','D','E';

numbers = 0;

output;

end;

run;

data out;

set real dummy;

run;

option missing = 0;

proc tabulate data=dummy missing;

where letters = 'E';

class letters/preloadfmt;

var numbers;

tables (letters='')*(

numbers * sum='' *f=comma24.0 )

/printmiss;

format letters $l.;

run;