Output Blank Tabulate

[wcp_fnfg]

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;

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;

[stat_sas]

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

[Cynthia_sas]

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;

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;

[wcp_fnfg]

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.

[wcp_fnfg]

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.

[Cynthia_sas]

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

[wcp_fnfg]

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.

[Cynthia_sas]

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

[wcp_fnfg]

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.     

[ballardw]

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

[wcp_fnfg]

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

[Cynthia_sas]

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

[wcp_fnfg]

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.

[ballardw]

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.

[Cynthia_sas]

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;