1.5. SAS PROCEDURES -> Equivalent in R

1.5.5. SAS PROC TRANSPOSE and its equivalent in R using pivot_wider() and pivot_longer()

Input Data: sales_data

1 Objective A: Pivot PRODUCT values into columns (wide format)

We want:

SAS Code

proc sort data=sales_data; by employee month;

proc transpose data=sales_data out=wide_product;
  by employee month;
  id product;
  var sales;
run;

Explanation:

• First, we sort by EMPLOYEE and MONTH because PROC TRANSPOSE’s BY requires sorted data.

• id product: turns values in PRODUCT into column names.

• var sales: fills the new columns with values from SALES.

R Code

# Dummy data for execution
library(dplyr)
library(tidyr)
sales_data <- tibble::tibble(
  EMPLOYEE = c("Alice", "Alice", "Alice", "Alice", "Bob", "Bob", "Bob", "Bob"),
  PRODUCT = c("Laptop", "Laptop", "Phone", "Phone", "Laptop", "Laptop", "Phone", "Phone"),
  MONTH = c("Jan", "Feb", "Jan", "Feb", "Jan", "Feb", "Jan", "Feb"),
  SALES = c(5000, 5200, 3000, 3100, 4600, 4800, 2800, 2900)
)
wide_product <- sales_data %>%
  pivot_wider(
    id_cols = c(EMPLOYEE, MONTH),
    names_from = PRODUCT,
    values_from = SALES
  )

Explanation:

• id_cols: defines keys to stay in rows.

• names_from: becomes column names (PRODUCT → Laptop, Phone).

• values_from: fills in those columns using SALES values.

• No sorting required!

2 Objective B: Reverse (wide → long format)

From the wide table above, convert back to original long form.

R Code

long_product <- wide_product %>%
  pivot_longer(
    cols = c(Laptop, Phone),
    names_to = "PRODUCT",
    values_to = "SALES"
  )

Explanation:

• cols: selects which columns to gather.

• names_to: stores column names in PRODUCT.

• values_to: stores numbers in SALES.

3 Objective C: Add a prefix to transposed column names

wide_with_prefix <- sales_data %>%
  pivot_wider(
    id_cols = c(EMPLOYEE, MONTH),
    names_from = PRODUCT,
    values_from = SALES,
    names_prefix = "SALE_"
  )

Explanation:

• Adds "SALE_" to every PRODUCT column name.

• Useful for labeling metrics like SCORE_*, RATE_*, etc.

4 Advanced Explorations

1. Multiple Value Columns

# Dummy data for execution
sales_multi <- tibble::tibble(
  EMPLOYEE = c("Alice", "Alice"),
  PRODUCT = c("Laptop", "Phone"),
  SALES = c(5000, 3000),
  PROFIT = c(1800, 1200)
)
pivot_wider(
  data = sales_multi,
  id_cols = EMPLOYEE,
  names_from = PRODUCT,
  values_from = c(SALES, PROFIT)
)

2. Use names_sep with Multi-Key Columns

pivot_wider(
  sales_data,
  id_cols = EMPLOYEE,
  names_from = c(PRODUCT, MONTH),
  values_from = SALES,
  names_sep = "_"
)

Output Columns: Laptop_Jan, Laptop_Feb, Phone_Jan, Phone_Feb

3. Use names_glue for Custom Naming

pivot_wider(
  sales_data,
  id_cols = EMPLOYEE,
  names_from = c(PRODUCT, MONTH),
  values_from = SALES,
  names_glue = "{PRODUCT}_SALES_{MONTH}"
)

4. Reverse wide → long format

pivot_longer(
  wide_product,
  cols = c(Laptop, Phone),
  names_to = "PRODUCT",
  values_to = "SALES"
)

5. Visualize Transposed Data

library(ggplot2)
ggplot(wide_product, aes(x = MONTH)) +
  geom_col(aes(y = Laptop, fill = EMPLOYEE), position = "dodge") +
  geom_col(aes(y = Phone, fill = EMPLOYEE), position = "dodge", alpha = 0.6)

Generates side-by-side bars for Laptop and Phone sales across employees and months.

**Summary Table**

**Resource download links**

1.5.5.-SAS-PROC-TRANSPOSE-and-its-equivalent-in-R-using-pivot_wider-and-pivot_longer.zip