← SASnRSelect Distinct Values Using SQL

Introduction

A quick "what are the unique values?" check is one of the most common tasks in clinical programming — listing treatment groups, visit names, or distinct subjects in a dataset.
SAS uses PROC SQL with SELECT DISTINCT to retrieve unique values. R has distinct() in dplyr and unique() in base R.
This lesson shows how to pull distinct values from one or more columns, and how to save the result as a new dataset.
We use a small ADSL-like dataset with duplicate treatment-by-sex combinations to make the deduplication visible.
After running, we confirm that only the unique combinations remain in the output.

Select Distinct Values Using SQL

SAS (Base SAS)+

*==============================================================================;
* Create sample ADSL data with duplicate treatment-sex combos;
*==============================================================================;

data adsl;
    input usubjid $ trt01p $ age sex $;
    datalines;
SUBJ001 Placebo 67 M
SUBJ002 DrugA   59 F
SUBJ003 Placebo 72 F
SUBJ004 DrugA   65 M
SUBJ005 Placebo 60 F
SUBJ006 DrugA   55 M
SUBJ007 Placebo 70 M
SUBJ008 DrugA   63 F
;
run;

*==============================================================================;
* SELECT DISTINCT on a single column;
*==============================================================================;

proc sql;
    select distinct trt01p
    from adsl;
quit;

*==============================================================================;
* SELECT DISTINCT on two columns;
*==============================================================================;

proc sql;
    select distinct trt01p, sex
    from adsl;
quit;

*==============================================================================;
* Save distinct values into a new dataset;
*==============================================================================;

proc sql;
    create table trt_list as
    select distinct trt01p
    from adsl;
quit;

proc print data=trt_list; run;

To get unique values in SAS, we use PROC SQL with the DISTINCT keyword after SELECT.
SELECT DISTINCT trt01p FROM adsl; returns one row per unique treatment group — Placebo and DrugA.
To find unique combinations across multiple columns, we list them after DISTINCT: SELECT DISTINCT trt01p, sex FROM adsl; returns one row for each treatment-sex pair.
To save the result for later use, we wrap it in CREATE TABLE name AS SELECT DISTINCT ....
The output from SELECT DISTINCT without CREATE TABLE prints directly to the output window — handy for a quick check.
After running, we confirm trt_list has exactly two rows: Placebo and DrugA.

R (tidyverse)+

library(tidyverse)

#==============================================================================
# Create sample ADSL data
#==============================================================================

adsl <- tribble(
  ~usubjid,  ~trt01p,    ~age, ~sex,
  "SUBJ001", "Placebo",  67,   "M",
  "SUBJ002", "DrugA",    59,   "F",
  "SUBJ003", "Placebo",  72,   "F",
  "SUBJ004", "DrugA",    65,   "M",
  "SUBJ005", "Placebo",  60,   "F",
  "SUBJ006", "DrugA",    55,   "M",
  "SUBJ007", "Placebo",  70,   "M",
  "SUBJ008", "DrugA",    63,   "F"
)

#==============================================================================
# Distinct values from a single column
#==============================================================================

adsl %>% distinct(trt01p)

#==============================================================================
# Distinct combinations from two columns
#==============================================================================

adsl %>% distinct(trt01p, sex)

#==============================================================================
# Save distinct values as a new tibble
#==============================================================================

trt_list <- adsl %>% distinct(trt01p)
trt_list

#==============================================================================
# Pull distinct values as a plain vector
#==============================================================================

adsl %>% distinct(trt01p) %>% pull(trt01p)

In dplyr, distinct() is the direct equivalent of SELECT DISTINCT — it returns unique rows for the specified columns.
adsl %>% distinct(trt01p) returns a tibble with one row per unique treatment group, dropping all other columns.
To get unique combinations, we list multiple columns: distinct(trt01p, sex).
We save the result to a new object with standard assignment: trt_list <- adsl %>% distinct(trt01p).
pull(trt01p) extracts the column as a plain character vector — useful when we need the values for a filter or a loop rather than a tibble.
By default, distinct() keeps only the columns we name. To keep all columns and deduplicate by selected ones, we add .keep_all = TRUE.
After running, we confirm trt_list has two rows and the pull() returns a vector of length 2.

Happy learning!

Search lessons

Introduction

Select Distinct Values Using SQL