A Terminology Map for SQL, R, and SAS

Table of Contents

Every language has its own name for the same concept. When you work with data, you usually have a go-to language, but you rarely get to use just one. Bouncing between tools, I’ve noticed that unless I keep the terminology organized somewhere, I end up mixing the terms myself. So this post is my attempt to sort it out once and for all.

Structures #

Concept	SQL	R	SAS
A table	table	data.frame, tibble, data.table	data set
A row	row, record, tuple	row	observation
A column	column, field, attribute	column, variable	variable
A single value	value, cell	element, cell	value

Verbs #

Rows are in SQL’s logical execution order.

Task	SQL	R (dplyr)	SAS
Join tables	`JOIN`	`*_join()`, `merge()`	`MERGE`
Filter rows	`WHERE`	`filter()`	`WHERE`, `IF`
Group and summarize	`GROUP BY`	`group_by()` + `summarize()`	`PROC MEANS`, `PROC SUMMARY`
Create a new column	`AS`	`mutate()`	`newvar=`
Select columns	`SELECT`	`select()`	`KEEP=`, `DROP=`
Remove duplicates	`DISTINCT`	`distinct()`, `unique()`	`PROC SORT NODUPKEY`
Sort	`ORDER BY`	`arrange()`	`PROC SORT`
Stack vertically	`UNION`	`bind_rows()`, `rbind()`	`SET`

Data Types #

Concept	SQL	R	SAS
Numbers	`NUMERIC`, `INT`, `FLOAT`, `DECIMAL`	`numeric` (=double), `integer`	`numeric`
Strings	`VARCHAR`, `CHAR`, `TEXT`	`character`	`character`
Categorical	(none)	`factor`	(none)
Dates	`DATE`, `TIMESTAMP`	`Date`, `POSIXct`	`date`
Boolean	`BOOLEAN`	`logical`	(none)

Missing Values #

Concept	SQL	R	SAS
Missing value	`NULL`	`NA`	`.` (numeric), (character)
Checking for missing	`IS NULL`	`is.na(x)`	`missing(x)`, `x = .`

I’ll keep extending this table as I run into more terminology collisions.