Character Functions
Prerequisites: Assignment statements and operators
SAS has a large library of text functions. The ones below cover most everyday cleaning.
data _null_;
name = 'ada lovelace';
a = propcase(name);
b = upcase(name);
len = length(name);
first3 = substr(name, 1, 3);
word2 = scan(name, 2);
pos = index(name, 'love');
swapped = tranwrd(name, 'ada', 'augusta');
put a= / b= / len= first3= word2= pos= / swapped=;
run;
a=Ada Lovelace
b=ADA LOVELACE
len=12 first3=ada word2=lovelace pos=5
swapped=augusta lovelace
(A / in a PUT statement starts a new line.)
| Function | Does |
|---|---|
upcase, lowcase, propcase |
change case |
length(s) |
length without trailing blanks |
substr(s, start, n) |
extract n characters from start (1-based) |
scan(s, n, delims) |
the nth word; -1 counts from the end |
index(s, text) / find(s, text, 'i') |
position of text (0 if absent); 'i' ignores case |
tranwrd(s, from, to) |
replace every occurrence of a word or phrase |
strip(s) |
remove leading and trailing blanks |
compress(s, chars) |
remove characters |
cats, catx(sep, ...) |
join, trimming blanks |
countw(s) |
number of words |
Cleaning messy text
data _null_;
phone = '(555) 123-4567';
digits = compress(phone, '()- ');
padded = ' hello ';
shown = cats('[', strip(padded), ']');
words = countw('the quick brown fox');
last = scan('red,green,blue', -1, ',');
put digits= / shown= / words= last=;
run;
digits=5551234567
shown=[hello]
words=4 last=blue
compress(s, , 'kd') — keep only digits — is another way to strip
everything but numbers.
Converting between text and numbers
INPUT(text, informat)reads text as a number (or date).PUT(value, format)turns a value into formatted text.
data _null_;
n = input('1,234', comma8.);
d = input('2024-03-15', yymmdd10.);
code = put(42, z5.);
label = put(d, date9.);
put n= d= code= label=;
run;
n=1234 d=23450 code=00042 label=15MAR2024
z5. pads with leading zeros — useful for IDs and ZIP codes.
Lengths of function results
A new variable created from a function like CATX, TRANWRD, or SCAN
usually gets a length of 200. That wastes space in large data sets;
set a sensible length first with LENGTH.
To test patterns interactively, the Regex Tester
helps — SAS's PRXMATCH and PRXCHANGE functions use Perl regular
expressions:
data _null_;
pos = prxmatch('/\d{3}-\d{4}/', 'call 555-1234 today');
put pos=;
run;
pos=6
Practice
Given full = 'Lovelace, Ada', create first and last and a variable
display that reads Ada LOVELACE.
Show solution
data _null_;
full = 'Lovelace, Ada';
length first last $ 20 display $ 41;
last = scan(full, 1, ',');
first = strip(scan(full, 2, ','));
display = catx(' ', first, upcase(last));
put display=;
run;
display=Ada LOVELACE