SAS:PROC TRANSPOSE (BY, ID, VAR, NAME=, PREFIX=, LET), then MERGE / SQL JOIN of SUPPxx onto the parent domain
PROC TRANSPOSE turns selected columns into rows. With ID, the values of that variable become column names. This site uses tidyr::pivot_longer() and tidyr::pivot_wider() for that pair. Base reshape() is not the form used here.
Quick reference
library(dplyr)library(tidyr)# long -> wide. PROC TRANSPOSE; by keys; id paramcd; var aval;lb |>pivot_wider(names_from = PARAMCD, values_from = AVAL)# wide -> long. NAME= is names_to; rename COL1 to the value columnwide |>pivot_longer(c(ALT, AST), names_to ="PARAMCD", values_to ="AVAL")# LET keeps the last duplicate ID. Without values_fn, pivot_wider warns.lb_dup |>pivot_wider(names_from = PARAMCD, values_from = AVAL, values_fn = last)# SUPPAE onto AE. QVAL stays character. IDVARVAL is character; AESEQ is integer.supp_wide <- suppae |>filter(RDOMAIN =="AE", IDVAR =="AESEQ") |>mutate(AESEQ =as.integer(IDVARVAL)) |>pivot_wider(id_cols =c(STUDYID, USUBJID, AESEQ),names_from = QNAM,values_from = QVAL )ae |>left_join( supp_wide,by =c("STUDYID", "USUBJID", "AESEQ"),relationship ="one-to-one" )
R
SAS
pivot_wider(names_from, values_from)
PROC TRANSPOSE with BY, ID, VAR
names_prefix = "AVAL_"
PREFIX=AVAL_ when ID is set
pivot_longer(cols, names_to, values_to)
PROC TRANSPOSE with VAR and NAME=; values land in COL1
values_fn = last
LET (last duplicate ID in the BY group)
left_join() after the widen
MERGE / SQL LEFT JOIN of SUPPxx onto the parent
Traps: QVAL stays character. IDVARVAL is character and AESEQ is numeric; the join does not match until those types agree. A blank QVAL stays "". A qualifier with no SUPP row is NA after pivot_wider(). In SAS both are character missing. A repeated ID in a BY group makes PROC TRANSPOSE error unless LET is set. pivot_wider() without values_fn warns and returns list-columns. PROC TRANSPOSE requires the BY variables to be sorted. SUPPxx is SDTM collected structure, not an ADaM imputation. A standard parent variable such as AEDUR is not a SUPP QNAM.
Worked examples below.
Long to wide
One row per subject and lab test. The wide table has one row per subject and one column per PARAMCD.
library(dplyr)
Attaching package: 'dplyr'
The following objects are masked from 'package:stats':
filter, lag
The following objects are masked from 'package:base':
intersect, setdiff, setequal, union
# A tibble: 2 × 3
USUBJID ALT AST
<chr> <dbl> <dbl>
1 STD-001-0001 22 24
2 STD-001-0002 31 28
SAS:
proc sort data=lb;
by usubjid;
run;
proc transpose data=lb out=lb_wide(drop=_name_);
by usubjid;
id paramcd;
var aval;
run;
BY variables must be sorted before PROC TRANSPOSE. ID values become the column names ALT and AST. VAR supplies the cells. _NAME_ holds the source variable name (AVAL); the drop= removes it. pivot_wider() does not add _NAME_ and does not require a sort.
PREFIX=AVAL_ together with ID names the columns AVAL_ALT and AVAL_AST. In R that prefix is names_prefix = "AVAL_".
# A tibble: 4 × 3
USUBJID PARAMCD AVAL
<chr> <chr> <dbl>
1 STD-001-0001 ALT 22
2 STD-001-0001 AST 24
3 STD-001-0002 ALT 31
4 STD-001-0002 AST 28
SAS:
proc transpose data=lb_wide out=lb_long(rename=(col1=aval)) name=paramcd;
by usubjid;
var alt ast;
run;
NAME= is names_to. Without an ID statement the values sit in COL1. Renaming COL1 is values_to. Without ID, PREFIX= replaces COL in COL1, COL2, .... That is a different naming rule from PREFIX= used with ID.
Warning: Values from `AVAL` are not uniquely identified; output will contain list-cols.
• Use `values_fn = list` to suppress this warning.
• Use `values_fn = {summary_fun}` to summarise duplicates.
• Use the following dplyr code to identify duplicates.
{data} |>
dplyr::summarise(n = dplyr::n(), .by = c(USUBJID, PARAMCD)) |>
dplyr::filter(n > 1L)
ALT for STD-001-0001 is a list of two numbers, not one result. PROC TRANSPOSE without LET stops when an ID value occurs twice in the same BY group. LET keeps the last occurrence in that group.
# A tibble: 2 × 3
USUBJID ALT AST
<chr> <dbl> <dbl>
1 STD-001-0001 99 24
2 STD-001-0002 31 28
values_fn = last keeps 99, the last ALT for STD-001-0001. That is the LET result. It does not check which row is the right one.
SUPPAE onto AE
SUPPxx is SDTM. Each row is one supplemental qualifier as collected: QNAM is the name, QVAL is the value. QVAL is character, including when the text looks numeric. IDVAR names the parent key (AESEQ). IDVARVAL is that key's value, stored as character. A blank IDVAR means the qualifier is subject-level, and the key is STUDYID plus USUBJID only.
This is not an ADaM imputation. AETRTEM and AENDAY are the qualifier names in this example. AENDAY is sponsor-defined: a day count, stored as text. It is not an AE domain variable. A standard AE variable such as AEDUR stays on AE. The IG does not allow it in SUPPQUAL, and AEDUR is an ISO 8601 duration, not a number of days.
STD-001-0001 sequence 2 has an AENDAY row whose QVAL is blank. STD-001-0002 sequence 1 has no AENDAY row.
Keep record-level qualifiers, convert IDVARVAL to the parent type, then widen. See Convert character, numeric, and date for as.integer() and for turning a numeric-looking QVAL into a number afterwards.
# A tibble: 3 × 5
STUDYID USUBJID AESEQ AETRTEM AENDAY
<chr> <chr> <int> <chr> <chr>
1 STD-001 STD-001-0001 1 Y "5"
2 STD-001 STD-001-0001 2 Y ""
3 STD-001 STD-001-0002 1 N <NA>
AETRTEM and AENDAY are character. The blank AENDAY is "". The absent AENDAY is NA.
SAS, after sorting by the BY variables:
proc transpose data=suppae(where=(rdomain="AE" and idvar="AESEQ"))
out=supp_wide(drop=_name_);
by studyid usubjid idvarval;
id qnam;
var qval;
run;
data supp_keyed;
set supp_wide;
aeseq = input(idvarval, best.);
run;
IDVARVAL is character. AESEQ on AE is numeric. input(idvarval, best.) is the SAS conversion before a MERGE on studyid, usubjid, and aeseq. Sort both tables by those keys first.
Joining AESEQ (integer) to IDVARVAL (character) fails.
Error in `left_join()`:
! Can't join `x$AESEQ` with `y$IDVARVAL` due to incompatible types.
ℹ `x$AESEQ` is a <integer>.
ℹ `y$IDVARVAL` is a <character>.
Convert first, then join on STUDYID, USUBJID, and AESEQ. The join rules are Merge on keys.
STUDYID USUBJID AESEQ AETERM AESEV AETRTEM AENDAY
1 STD-001 STD-001-0001 1 Headache MILD Y 5
2 STD-001 STD-001-0001 2 Nausea MODERATE Y
3 STD-001 STD-001-0002 1 Fatigue MILD N <NA>
ImportantA blank QVAL is not a missing SUPP row
Sequence 2 for STD-001-0001 has AENDAY equal to "": the qualifier was submitted blank. Sequence 1 for STD-001-0002 has AENDAY equal to NA: SUPPAE has no AENDAY row for that event. pivot_wider() does not turn "" into NA. In SAS both results are character missing, so the transposed column cannot show the difference.
QVAL is still character after the join. A sponsor-defined day count has to be converted before it is used as a number. as.numeric(na_if(AENDAY, "")) turns the blank into NA, and the already missing qualifier stays NA. After that step the two reasons are gone. Keep the character value, or a flag, if a later step must tell them apart.