Automatically Converting Character Variables That Store ...
NESUG 2010
Foundations and Fundamentals
Automatically Converting Character Variables
That Store Numbers to Numeric Variables
Christopher J. Bost, MDRC, New York, NY
ABSTRACT
A character variable can store numbers, but many analyses require numeric variables. This paper shows how to
convert character variables that store numbers to numeric variables. The DATA step, PROC SQL, and the SAS?
macro language are used to automate the process. The result is a new data set that stores all variables with the
same names, labels, and order, but with character variables that stored numbers converted to numeric variables.
INTRODUCTION
Numbers can be stored in numeric variables or character variables. For example, a dummy variable that takes
the values 1 and 0 could be numeric or character. SAS? will even perform an automatic character-to-numeric
conversion as needed in specific situations (e.g., when a character variable is compared to a numeric variable).
System resources, however, are required to convert variables. It is not efficient to have SAS perform automatic
conversions. Moreover, if SAS tries to convert a character value that contains a non-numeric value to a number
it produces an error. In addition, the majority of statistical procedures require analysis variables to be numeric.
Character variables that store numbers and that are treated like numeric variables can be explicitly converted
to numeric variables.
SAMPLE DATA
SAS data set EARNINGS is used in this paper. It contains six variables: ID, EARNQ1 through EARNQ4 (four
quarterly earnings variables, in dollars), and EMPLOYED (a dummy variable for any earnings in quarters 1-4):
CONTENTS of data set EARNINGS
#
Variable
Type
Len
1
2
3
4
5
6
id
earnq1
earnq2
earnq3
earnq4
employed
Char
Num
Num
Char
Char
Char
2
8
8
4
5
3
Label
Unique identifier
Earnings in Quarter
Earnings in Quarter
Earnings in Quarter
Earnings in Quarter
Ever employed
1
2
3
4
Note that ID, EARNQ3, EARNQ4, and EMPLOYED are character variables.
PRINT of data set EARNINGS
Obs
id
earnq1
earnq2
earnq3
earnq4
employed
1
2
3
4
5
01
02
03
04
05
1000
0
0
1400
0
1300
2000
0
1400
.
1400
1250
2300
0
900.0
0
1
1
UNK
1
UNK
0
1500
0
ID values contain leading zeroes that should be preserved. EMPLOYED contains the character value ¡®UNK¡¯ (for
¡®Unknown¡¯). EARNQ3 and EARNQ4, however, store only numbers and could be converted to numeric variables.
1
NESUG 2010
Foundations and Fundamentals
CONVERTING VARIABLES
The following steps are required to convert character variables that store numbers to numeric variables:
1. Identifying character variables to convert
2. Creating equivalent numeric variables
3. Dropping the original character variables
4. Renaming the equivalent numeric variables
5. Reordering variables to the original sequence
6. Labeling the equivalent numeric variables
The required statements are hard-coded in the next section to illustrate this process. The required statements
are then generated by SAS in the subsequent section to automate this process.
CONVERTING VARIABLES MANUALLY
STEP 1: IDENTIFYING CHARACTER VARIABLES TO CONVERT
Use PROC FREQ to produce frequency tables for all character variables:
proc freq data=earnings;
tables _character_;
run;
The TABLES statement uses the special SAS name list _CHARACTER_ to reference all character variables.
Inspect the results for non-numeric values. If a variable contains only numeric values, it could be converted
to a numeric variable:
The FREQ Procedure
Unique identifier
Cumulative
Cumulative
id
Frequency
Percent
Frequency
Percent
------------------------------------------------------01
1
20.00
1
20.00
02
1
20.00
2
40.00
03
1
20.00
3
60.00
04
1
20.00
4
80.00
05
1
20.00
5
100.00
Earnings in Quarter 3
Cumulative
Cumulative
earnq3
Frequency
Percent
Frequency
Percent
----------------------------------------------------------0
2
50.00
2
50.00
1400
1
25.00
3
75.00
1500
1
25.00
4
100.00
Frequency Missing = 1
(continued)
ID contains no non-numeric values. It is a unique identifier, however, and its leading zeroes should be preserved.
EARNQ3 contains only numeric values (plus a missing value). It could be converted to a numeric variable.
2
NESUG 2010
Foundations and Fundamentals
(continued)
Earnings in Quarter 4
Cumulative
Cumulative
earnq4
Frequency
Percent
Frequency
Percent
----------------------------------------------------------0
2
40.00
2
40.00
1250
1
20.00
3
60.00
2300
1
20.00
4
80.00
900.0
1
20.00
5
100.00
Ever employed
Cumulative
Cumulative
employed
Frequency
Percent
Frequency
Percent
------------------------------------------------------------1
3
60.00
3
60.00
UNK
2
40.00
5
100.00
EARNQ4 also contains only numeric values (including a value with a decimal point). It could be converted to a
numeric variable. EMPLOYED contains the value ¡®UNK¡¯. It could not be converted to a numeric variable.
STEP 2: CREATING EQUIVALENT NUMERIC VARIABLES
Use the INPUT function in an assignment statement to convert a character variable to a numeric variable:
x1=input(earnq3,best12.);
x2=input(earnq4,best12.);
The INPUT function reads each value of character variable EARNQ3 with the numeric informat BEST12.,
converts it to a numeric value, and stores it in numeric variable X1. The same applies to EARNQ4 and X2.
STEP 3: DROPPING THE ORIGINAL CHARACTER VARIABLES
Use the DROP statement to drop the original character variables that stored numbers:
drop earnq3 earnq4;
STEP 4: RENAMING THE EQUIVALENT NUMERIC VARIABLES
Use the RENAME statement to rename the equivalent numeric variables to the original character variable names:
rename x1=earnq3;
rename x2=earnq4;
Note that a variable¡¯s type, character or numeric, cannot be changed once the variable is created. A new SAS data
set needs to be created to store the equivalent numeric variables. Steps 2-4 can be consolidated in one DATA step:
data earnings2;
set earnings;
x1=input(earnq3,best12.);
x2=input(earnq4,best12.);
drop earnq3 earnq4;
rename x1=earnq3;
rename x2=earnq4;
run;
Also note that the equivalent numeric variables are added after the original variables and do not have labels:
3
NESUG 2010
Foundations and Fundamentals
CONTENTS of data set EARNINGS2
#
Variable
Type
Len
1
2
3
4
5
6
id
earnq1
earnq2
employed
earnq3
earnq4
Char
Num
Num
Char
Num
Num
2
8
8
3
8
8
Label
Unique identifier
Earnings in Quarter 1
Earnings in Quarter 2
Ever employed
STEP 5: REORDERING VARIABLES TO THE ORIGINAL SEQUENCE
Use the RETAIN statement to reorder variables to the original sequence:
data earnings3;
retain id earnq1-earnq4 employed;
set earnings2;
run;
Note that SAS adds variables to the data set being created in the order in which it encounters them. Placing the
RETAIN statement after the DATA statement, but before the SET statement, effectively adds the variables named
on the RETAIN statement to the data set being created in the specified order. Numbered range lists of variables
(EARNQ1-EARNQ4) can be used.
SAS picks up the variable attributes (type and length, plus format and/or label, if assigned) when it executes the
SET statement and reads the descriptor portion of data set EARNINGS2.
STEP 6: LABELING THE EQUIVALENT NUMERIC VARIABLES
Use the LABEL statement to label the equivalent numeric variables with the original character variable labels:
label earnq3='Earnings in Quarter 3';
label earnq4='Earnings in Quarter 4';
Note that a new SAS data set needs to be created to reorder variables. Variables can be labeled with the LABEL
statement at the same time. Steps 5-6 can be consolidated in one DATA step:
data earnings3;
retain id earnq1-earnq4 employed;
set earnings2;
label earnq3='Earnings in Quarter 3';
label earnq4='Earnings in Quarter 4';
run;
Character variables that stored numbers have now been converted to numeric variables with the same names,
labels, and order:
CONTENTS of data set EARNINGS3
#
Variable
Type
Len
1
2
3
4
5
6
id
earnq1
earnq2
earnq3
earnq4
employed
Char
Num
Num
Num
Num
Char
2
8
8
8
8
3
Label
Unique identifier
Earnings in Quarter
Earnings in Quarter
Earnings in Quarter
Earnings in Quarter
Ever employed
4
1
2
3
4
NESUG 2010
Foundations and Fundamentals
PRINT of data set EARNINGS3
Obs
id
earnq1
earnq2
earnq3
earnq4
employed
1
2
3
4
5
01
02
03
04
05
1000
0
0
1400
0
1300
2000
0
1400
.
1400
.
0
1500
0
1250
2300
0
900
0
1
1
UNK
1
UNK
The above six steps produce the desired result but require coding that would be prohibitive with a larger number
of variables. The next section describes how to generate the same code using SAS.
CONVERTING VARIABLES AUTOMATICALLY
STEP 1: IDENTIFYING CHARACTER VARIABLES TO CONVERT
First, identify character variables in SAS data set EARNINGS. Use PROC SQL to retrieve their names from a
DICTIONARY table.
DICTIONARY.COLUMNS stores the names and types of all variables in all data sets with one variable per row.
Do not retrieve ID; although it stores only numeric values, they contain leading zeroes that should be preserved:
%let excludevars=id;
proc sql noprint;
select name into :charvars separated by ' '
from dictionary.columns
where libname="WORK" and memname="EARNINGS" and type="char"
and not indexw(upcase("&excludevars"),upcase(name));
quit;
The %LET statement assigns the value ID to a macro variable named EXCLUDEVARS.
The PROC SQL statement starts the procedure. The NOPRINT option suppresses printed output.
The SELECT clause picks up values of NAME (variable name). The INTO clause stores values in a macro variable
named CHARVARS, separated by blanks.
The FROM clause retrieves rows from DICTIONARY.COLUMNS.
The WHERE clause subsets rows where four conditions are met:
1. the value of LIBNAME equals ¡°WORK¡±
2. the value of MEMNAME equals ¡°EARNINGS¡±
3. the value of TYPE equals ¡°char¡±
4. the value of NAME is not in the list of variables to be excluded
(Note: Values of LIBNAME and MEMNAME are stored in uppercase and values of TYPE are stored in lowercase.)
In other words, the WHERE clause subsets rows for character variables in SAS data set WORK.EARNINGS that
are not specifically excluded.
The INDEXW function returns the starting position of the second argument if it is found in the first argument or 0
if it is not found. Variable names may be in uppercase, lowercase, or mixed case; the UPCASE function is used on
both arguments to compare values in the same case. When the expression is NOT true (i.e., when the result is 0,
meaning the variable name was not found in the list of variables to exclude), the row is selected.
Second, determine the number of character variables:
%let ncharvars=%sysfunc(countw(&charvars));
The %LET statement uses the COUNTW function to count the number of words (i.e., variable names) stored in macro
variable CHARVARS. COUNTW is a DATA step function; it must be called by %SYSFUNC in a %LET statement.
5
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- automatically replace words in word
- variables that affect photosynthesis
- character traits that start with i
- automatically renewed contract
- automatically sign into hotmail inbox
- outlook automatically signs me in
- automatically forward messages outlook
- character traits that begin with y
- automatically number rows in excel
- automatically open documents in word
- automatically open pdf in adobe
- start chrome automatically windows 10