Format missing values in a table for publication

You are making a table for publication, and you want to change how missing values (NAs) are displayed.

Step 1 - Start with a data frame that contains the values that you want to display in the arrangement that you want to display them.

Step 2 - Use gt::gt() to turn your data frame into an editable, professional looking table.

Step 3 - Use gt::sub_missing() to format the missing values in your table. sub_missing() will replace every NA with the value of missing_text. Set the columns and rows arguments to restrict substitutions to specific parts of the table.

data %>% 
  gt() %>% 
  sub_missing(
    columns = 3:5,
    missing_text = "--"
  )

Example

store_sales describes the number of various Quaker oatmeal products sold at three stores during a week.

store_sales

# A tibble: 4 × 4
  product                    store8 store21 store44
  <chr>                       <int>   <int>   <int>
1 Quaker Instant Grits 12 oz     20      12       3
2 Quaker Kids Choice 14.3 oz      3       7      NA
3 Quick Quaker Oats 18 oz        NA      21      53
4 Quick Quaker Oats 42 oz       123      18      52

Before we publish the table, we’d like to replace every NA with 0. To do this, we load the gt package and then convert our data frame to a gt table. Then we use sub_missing() to replace every NA in our dataset with 0.

library(gt)
store_sales %>% 
  gt() %>% 
  sub_missing(missing_text = 0)

product	store8	store21	store44
Quaker Instant Grits 12 oz	20	12	3
Quaker Kids Choice 14.3 oz	3	7	0
Quick Quaker Oats 18 oz	0	21	53
Quick Quaker Oats 42 oz	123	18	52

Missing values in SAS tables

Tables for publication can be created in SAS using either the TABULATE or REPORT procedure. The TABULATE procedure has a MISSTEXT option to fill in missing values, whereas the REPORT procedure necessitates the global options.

In R, you create the table and then convert it to publication quality. However, in SAS you create the table and make it look nice all in the same step.

In SAS:

PROC TABULATE DATA = quaker_sales;
  CLASS product store_id;
  VAR units_sold;
  TABLE product, 
      (store_id * units_sold) * SUM / 
      MISSTEXT = '0';
RUN;

or

OPTIONS MISSING = 0;

PROC REPORT DATA = quaker_sales;
    COLUMN product (store_id, units_sold);
    DEFINE product / GROUP;
    DEFINE store_id / ACROSS;
    DEFINE units_sold / COMPUTED SUM;
RUN;

In R:

quaker_sales %>% 
  pivot_wider(
    names_from = store_id,
    values_from = units_sold
  ) %>% 
  gt() %>% 
  sub_missing(
    columns = -1,
    missing_text = 0
  )