put(siteid, z3.) turns 7 into "007". In R, use sprintf("%03d", siteid) or stringr::str_pad(siteid, 3, pad = "0"). To clean blanks, STRIP removes leading and trailing blanks, COMPBL squeezes repeated blanks to one, and COMPRESS removes them all. The R equivalents are trimws(), str_squish() and gsub().Identifiers and text fields cause more join failures than any complex derivation. A site stored as 7 in one dataset and "007" in another, or an adverse event term with a double space, is enough to make a merge miss records or a frequency table split one term into two. Both problems have one-line fixes once you know which function does what.
Building USUBJID with leading zeros
A common sponsor convention: USUBJID = study, a 3-digit site and a 4-digit subject number. The raw data stores site and subject as plain numbers.
data dm;
input studyid $ siteid subjno;
datalines;
CSP01 7 1
CSP01 12 23
CSP01 105 4
;
run;
data dm2;
set dm;
length usubjid $20;
usubjid = catx('-', studyid, put(siteid, z3.), put(subjno, z4.));
run;
library(dplyr)
dm <- tibble::tribble(
~STUDYID, ~SITEID, ~SUBJNO,
"CSP01", 7L, 1L,
"CSP01", 12L, 23L,
"CSP01", 105L, 4L
)
dm2 <- dm %>%
mutate(USUBJID = paste(STUDYID,
sprintf("%03d", SITEID),
sprintf("%04d", SUBJNO),
sep = "-"))
Expected output:
| SITEID | SUBJNO | USUBJID |
|---|---|---|
| 7 | 1 | CSP01-007-0001 |
| 12 | 23 | CSP01-012-0023 |
| 105 | 4 | CSP01-105-0004 |
Zw. works like the plain w. format but pads with zeros instead of blanks. The width is the total number of characters, so it must cover the largest value you will ever have. If a study can open site 1000, z3. is the wrong choice. Check the maximum before you fix the width.
The format also takes decimals: put(3.5, z6.2) gives "003.50", six characters including the point. That is rarely needed for IDs but helps with fixed-width export files.
Other R options
formatC(7L, width = 3, format = "d", flag = "0") # "007"
stringr::str_pad(7, width = 3, pad = "0") # "007"
str_pad() pads on the left by default and also works on character input, which is handy when the ID is already text. sprintf("%03d", x) needs whole numbers. A value like 7.5 gives an error, which is a useful check in itself.
When the number is already character
If SITEID arrives as character, the Z format can't be applied directly because it is a numeric format. Convert first:
site3 = put(input(site_c, 8.), z3.);
If site_c could hold non-numeric text, add ?? to the informat and list the values that fail, rather than letting them turn into a missing site in the ID.
Removing extra blanks
Text from CRFs and free-text fields often has leading blanks, double spaces or both. Here is what each function does with two adverse event terms (quotes added to show the blanks):
data ae;
length aeterm $40;
aeterm = ' Severe headache'; output;
aeterm = 'Nausea and vomiting'; output;
run;
data ae_clean;
set ae;
length t_strip t_compbl t_both t_compress $40;
t_strip = strip(aeterm);
t_compbl = compbl(aeterm);
t_both = strip(compbl(aeterm));
t_compress = compress(aeterm);
run;
library(stringr)
aeterm <- c(" Severe headache", "Nausea and vomiting")
trimws(aeterm) # strip ends
gsub(" +", " ", aeterm) # like COMPBL
str_squish(aeterm) # ends + internal runs
gsub(" ", "", aeterm, fixed = TRUE) # like COMPRESS
Expected values, with quotes added so the blanks are visible:
Function Row 1 Row 2
Original " Severe headache" "Nausea and vomiting"
STRIP / trimws() "Severe headache" "Nausea and vomiting"
COMPBL / gsub(" +", " ") " Severe headache" "Nausea and vomiting"
STRIP(COMPBL()) / str_squish "Severe headache" "Nausea and vomiting"
COMPRESS / gsub(" ", "") "Severeheadache" "Nauseaandvomiting"
Two details to notice. COMPBL reduces the two leading blanks to one but does not remove it, so you usually want STRIP(COMPBL(x)). And COMPRESS with no second argument removes every blank, which is right for codes and IDs and wrong for terms.
COMPRESS for more than blanks
The second and third arguments make COMPRESS a general character filter:
no_dash = compress('CSP01-007-0001', '-'); /* CSP010070001 */
digits = compress('Site 007', , 'kd'); /* 007: k = keep, d = digits */
The R equivalents are gsub("-", "", x) and gsub("[^0-9]", "", x).
Where blanks come from in the first place
SAS character variables are fixed length and padded with trailing blanks. The || operator keeps that padding, so studyid || '-' || site3 with studyid stored as $8 gives CSP01, then three blanks, then -007. CATX, CATS and CATT strip or trim their arguments, which is why CATX was used for USUBJID above.
If a value still looks like it has a blank after STRIP and COMPBL, it may be a different character, such as a tab or a non-breaking space copied from a spreadsheet. Print the value with the $HEX. format to see the actual bytes before choosing a fix.
Common mistakes
- A Z width that is too small. Size it for the largest possible value, not the largest in today's data.
- Applying Z to a character variable. Convert with
INPUTfirst. - Joining on IDs built two ways. If one dataset has
"7"and another"007", the merge silently misses records. Build the ID once, in one place. - COMPBL alone. One leading blank survives. Wrap it in
STRIP. - COMPRESS on terms. It glues words together. Use it on codes, not text.
- Concatenating with ||. Trailing blanks end up inside the value. Use
CATX.
Try it yourself: rebuild a broken ID
A vendor file has subj = " 12-7 ", meaning site 12, subject 7, with stray blanks. Build "CSP01-012-0007" in SAS and in R.
Answer: In SAS:
s = strip(subj);
usubjid = catx('-', 'CSP01',
put(input(scan(s, 1, '-'), 8.), z3.),
put(input(scan(s, 2, '-'), 8.), z4.));
In R:
parts <- strsplit(trimws(" 12-7 "), "-")[[1]]
paste("CSP01", sprintf("%03d", as.integer(parts[1])),
sprintf("%04d", as.integer(parts[2])), sep = "-")
# "CSP01-012-0007"
Quick check
1. What does put(45, z4.) return?
Zw. right-aligns the number in w characters and fills the left with zeros.
2. A term has two leading blanks and three blanks between its two words. Which call returns "Severe headache"?
COMPBL leaves one leading blank and COMPRESS removes the space between the words. STRIP around COMPBL gives the clean term.
Frequently asked questions
How do I add leading zeros to a number in SAS?
Use the Zw. format with PUT, for example put(siteid, z3.). The result is character, padded with zeros to w characters.
How do I add leading zeros in R?
sprintf("%03d", x) for whole numbers, formatC(x, width = 3, format = "d", flag = "0"), or stringr::str_pad(x, 3, pad = "0").
What is the difference between COMPBL and COMPRESS?
COMPBL replaces each run of blanks with a single blank. COMPRESS removes blanks completely, or any characters you list in its second argument.
What is the R equivalent of STRIP and COMPBL?
trimws() replaces STRIP. stringr::str_squish() does STRIP and COMPBL together.
put(x, zw.)in SAS andsprintf("%0wd", x)in R add leading zeros. Size w for the largest possible value.- Build IDs once with
CATXorpaste(), so every dataset joins on the same string. STRIP(COMPBL(x))andstr_squish()are the safe cleanup for text. KeepCOMPRESSfor codes.- A blank that won't go away is probably another character. Look at it with
$HEX..
Related: Following one adverse event from the CRF to the table · Special characters in SAS outputs · Building test data in SAS and R