← SASnRKeep Only Variables with Non-missing Values Across Dataset

Introduction

In real clinical data, some expected columns are never collected — every row is missing for them.
A useful clean-up pass: drop any column whose values are entirely missing across the dataset, keeping only columns that carry at least one real observation.
SAS does this by walking the _character_ and _numeric_ arrays, collecting variable names with at least one non-missing value, and passing that list to a KEEP= option via call symputx.
Tidyverse uses select(where(...)) with a predicate that returns TRUE when any value in the column is non-missing.
Base R uses sapply() to build a column-level logical mask, then subsets the data frame with that mask.
This lesson cleans up a lab dataset where ldh and crp are fully missing, dropping those two columns from the result.

Keep Only Variables with Non-missing Values Across Dataset

SAS (Base SAS)+

data lab;
    input usubjid $ hgb glu chol$ alb alt$ ast ldh$ crp;
    datalines;
101 12.5 110 POS 4.2 NEG 21 . .
102 13.0 105 NEG 4.1 NEG 25 . .
103 . . . . . . . .
104 14.1 108 POS 4.5 NEG 20 . .
;
run;

data _null_;
    set lab end=last;

    array charvars {*} _character_;
    array numvars {*} _numeric_;

    length keep_chars keep_nums $32767;

    retain keep_chars keep_nums;

    do i = 1 to dim(charvars);
        if charvars{i} ne '' then
            keep_chars = catx(' ', keep_chars, vname(charvars{i}));
    end;

    do j = 1 to dim(numvars);
        if numvars{j} ne . then
            keep_nums = catx(' ', keep_nums, vname(numvars{j}));
    end;

    if last then
        call symputx('keep_vars', catx(' ', keep_chars, keep_nums));
run;

data lab_clean;
    set lab(keep=&keep_vars.);
run;

In SAS, the trick is to inspect every variable, collect the names of the ones with at least one non-missing value, and rebuild the dataset using a KEEP= list.
A data _null_; step walks the rows without producing output, using two arrays: array charvars {*} _character_; and array numvars {*} _numeric_;.
Two RETAINed accumulators (keep_chars, keep_nums) grow across rows; we use catx(' ', ...) to append names as we discover non-missing values.
vname(charvars{i}) returns the original variable name associated with the i-th array element — exactly what a KEEP list needs.
On the last row (set lab end=last;), call symputx('keep_vars', catx(' ', keep_chars, keep_nums)); stores the final list into a macro variable.
A second DATA step then rebuilds the output: data lab_clean; set lab(keep=&keep_vars.); run;.
We run and confirm LAB_CLEAN has the same rows as LAB but drops the fully-missing columns ldh and crp.

R (tidyverse)+

library(tidyverse)

lab <- tribble(
~usubjid, ~hgb, ~glu, ~chol, ~alb, ~alt, ~ast, ~ldh, ~crp,
"101", 12.5, 110, "POS", 4.2, "NEG", 21, NA, NA,
"102", 13.0, 105, "NEG", 4.1, "NEG", 25, NA, NA,
"103", NA, NA, NA, NA, NA, NA, NA, NA,
"104", 14.1, 108, "POS", 4.5, "NEG", 20, NA, NA )
 

lab_clean <- lab %>%
select(where(~ any(!is.na(.) & . != "")))

In tidyverse, select(where(...)) is the one-line version: keep only columns that pass a predicate.
select(where(~ any(!is.na(.) & . != ""))) keeps columns that have at least one non-missing and non-blank value.
The ~ introduces an anonymous predicate; . stands for the current column vector.
any(!is.na(.) & . != "") returns TRUE if any value in the column is both non-NA and not an empty string — useful when a column has an NA alongside blanks.
The resulting lab_clean keeps the same row count as lab but sheds the fully-missing columns.
We run and compare column counts before and after: the drop should match whichever columns are entirely missing in the source data.

Happy learning!

Search lessons

Introduction

Keep Only Variables with Non-missing Values Across Dataset