24590 - Convert variable values from character to numeric or from numeric to character

Sample 24590: Convert variable values from character to numeric or from numeric to character

The INPUT and PUT functions convert values for a variable from character to numeric, and from numeric to character. A variable can be defined as only one type, so you cannot use the same variable name to convert the values. When you use the functions below, you create a new variable with the values converted. If you want the end result to be the original variable name, you need to drop the original variable and rename the new variable to the old name.

Note: The examples listed below use hardcoded values for simplicity. See the Full Code tab for examples of how to perform these actions when reading a data set.

Convert character to numeric

To convert character values to numeric values, use the INPUT function.

new_variable = input(original_variable, informat.);

The informat tells SAS how to interpret the data in the original character variable.

For example, if you have a simple string of digits like 12345678, you can use the basic numeric informat w.d:

data new;

   char_var = '12345678';

   numeric_var = input(char_var, 8.);

run;

If you want your resulting data set to use the original variable name as a different type, you need to drop the original variable and rename the new variable to the old name:

data new;

   orig = '12345678';

   new = input(orig, 8.);

   drop orig;

   rename new=orig;

run;

If you want to display the leading zeros in your numeric value as they were shown in your character string, you need to use the basic numeric informat w.d and then apply the format Zw.d to the numeric variable.

char_var = '0012';

numeric_var = input(char_var, $4.);

format numeric_var z4.;

If your string contains nondigits such as commas or dollar signs, you need to use the correct informat:

char_var = '12,000,000';

numeric_var = input(char_var,comma10.);

char_var = '$6,000,000';

numeric_var = input(char_var,dollar10.);

As with all numeric values, the data is saved with only numbers in the numeric_var variable. If you were to display it, you would see 6000000. You can use the FORMAT statement to display the new variable in the desired format.

format numeric_var dollar10.;

The informat and format documentation lists the choices by type, so you can see which one matches your data. If you choose an informat that does not match your data or have some values that do not match, using the INPUT function results in a missing value. A NOTE like the following is also displayed in the SAS log:

NOTE: Invalid argument to function INPUT at line 725 column 8.

If you know that you have some invalid data in the original variable and do not want every NOTE listed in your SAS log, you can suppress the printing of these notes by using the ? or ?? modifier with the informat:

numeric_var = input(char_var, ? 8.);

See the INPUT function documentation for more information.

You can use the CONTENTS procedure to ensure that the new variable name is the type that you expect.

proc contents data=data_set_name;

run;

Using the INPUT function to convert a character date to a SAS date

A SAS date is a numeric value with which you can use date functions and calculations. A SAS date might be formatted so that it contains characters in the display, but it is always stored as a numeric variable.

For example, suppose you receive a data set that contains a variable named Startdate that is displayed in PROC PRINT output as 12JUL2016.

Is Startdate a character string "12JUL2016" or is it a numeric variable with a format applied? You can use PROC CONTENTS to verify the variable type and to see whether a format has been applied to it. If it is a character variable, you can convert it to a SAS date so that you can take advantage of the SAS date functions. Look at the date informats to determine which informat matches your values. In this case, DATE9. reads date values in the form ddmmmyy, which matches 12JUL2016.

The following code starts with a character string "12JUL2016", creates a SAS date, and then formats it with the DATE9. format. The end result is a SAS date that looks the same as the original variable, but can be analyzed and manipulated by the date functions:

startdate = "12JUL2016";

date_var = input(startdate,date9.);

format date_var date9.;

The same methodology applies to time and datetime values. Click the Full Code tab to see examples of these.

Convert numeric values to character

To convert numeric values to character, use the PUT function:

new_variable = put(original_variable, format.);

The format tells SAS what format to apply to the value in the original variable. The format must be of the same type as the original variable. For example, if you are using the PUT function to convert a numeric value to character, the format must be a numeric format.

For example, if you have the numeric value 123456, you can use the basic numeric format w.d:

num_var = 123456;

char_var = put(num_var,6.);

When you start with a numeric value, the resulting string is right-aligned by default. This means that if you use a format in the PUT function that is longer than the value being converted, the resulting character value is right aligned. If you want it to be left-aligned, you can use the -L specification with the PUT function:

char_var = put(num_var, 10. -L);

Putting it all together

You have seen how to convert numeric values to character and character values to numeric. Both of these steps are needed to convert a numeric value that looks like mmddyyyy but is not a SAS date, to a SAS date.

Start with a numeric variable with a value of 10122012 as an example. This looks like a SAS date, but when you use PROC CONTENTS, you see that there is no format applied, so it is actually the integer value 10122012. You want it to be a SAS date with a format that displays the value as 10122012. How do you convert a numeric value to a SAS date?

Click the Full Code tab to see the solution for this question and many more examples.

For even more information about converting values, see the following SAS tutorial video:

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

The examples below range from simple to more complex.
Be sure to run PROC CONTENTS periodically to see the changes that are made to the variables.
Example 9 contains the code for "Putting it all together".
Examples 10 and 11 show how to perform these actions on a variable in a data set.

/* Example 1: The simplest case character to numeric */
/* The resulting data set has both the original character variable and a new */
/* numeric variable.  If you want the result to have just one variable with  */
/* the original name, uncomment the DROP and RENAME statements. */

data new1;
   orig_var = '80000';
   new_var = input(orig_var,8.);
/* drop orig_var; */
/* rename new_var = orig_var; */
run;
proc print; 
run;

/* Example 2a and 2b: Character to numeric with additional characters in original string */
/* You can comment out the FORMAT statements to see what the unformatted value looks like. */

data new2a;
   orig_var = "12%";
   new_var = input(orig_var, percent8.);
   format new_var percent8.;
run;
proc print;
run;

data new2b;
   orig_var = "43,000.24";
   new_var = input(orig_var, comma9.2);
   format new_var comma9.2;
run;
proc print; 
run;

/* Example 3: Multiple input values with a blank character value for one of the values. */
/* The result has a standard numeric missing value of . for the converted value.  */

data old3;
   infile datalines truncover;
   input orig_var :$12.;
   datalines;
1234
0
99999

56
;
run;

data new3;
   set old3;
   new_var = input(orig_var,8.);
run;
proc print; 
run;

/* Example 4: Convert a character date to a SAS date.  By looking through the informat */
/* documentation, I see that the structure mm-dd-yyyy is read by the MMDDYY10. informat. */
/* I have chosen to format the resulting SAS date with the DATE9. format. */

data new4;
   orig_var = '04-23-2013';
   new_var = input(orig_var,mmddyy10.);
   format new_var date9.;
run;
proc print; 
run;

/* Example 5a: Convert a character time value to a SAS time */
/* variable and format it to look the same as the original value. */

data new5a;
   orig_var = '10:12:34';
   new_var = input(orig_var,time8.);
   format new_var time8.;
run;
proc print; 
run;

/* Example 5b: Same as 5a but ends up with the same name for the resulting variable.*/
/* Format it to look the same as the original.  */

/* The RENAME statement affects the name in the output data set, not during the current */
/* DATA step.  During the current DATA step, you use the old variable name in programming */
/* statements.  That is why you see the FORMAT on New_var (the initial variable name for */
/* the created variable), but the resulting output has the format inherited by the new */
/* name from the RENAME which is Orig_var. */

/* For more information, see the Statements Reference documentation.*/

data new5b;
   orig_var = '10:12:34';
   new_var = input(orig_var, time8.);
   drop orig_var;
   rename new_var = orig_var;
   format new_var time8.;
run;
proc print;
run;

/* Example 6: Convert a character datetime to a SAS datetime */

data new6;
   orig_var = '12MAY2014:01:13:55';
   new_var = input(orig_var,datetime20.);
   format new_var datetime20.;
run;
proc print;
run;

/* Example 7: Numeric to character and end up with the original variable name */

data new7;
   orig_var = 189;
   new_var = put(orig_var,8.);
   drop orig_var;
   rename new_var = orig_var;
run;
proc print;
run;

/* Example 8: Numeric to character with left alignment using DATALINES to read in */
/* multiple numeric values.  You can remove the -L to see the different result in the */
/* output without it. */

data new8;
   input orig_var 8.;
   new_var = put(orig_var, 12. -L);
   datalines;
123
8345521
.
99
;
run;
proc print;
run;

/* Example 9: Putting it all together: Convert a numeric value that looks like */
/* MMDDYYYY, but is a true numeric value with no format applied, to a SAS date. */

data new9;
   orig_var = 10122012;
   char_var = put(orig_var,8.);
   sas_date = input (char_var,mmddyy8.);
   format sas_date mmddyy8.;
run;
proc print;
run;

/*Example 10: Convert all values in a data set variable from character to numeric.*/
/*Score was entered as a character value, but we need it to be numeric so we can do calculations*/
/*on it.  We want to end up with it still named score, so we need to drop the original and rename the new */
/*variable to score.*/

data results;
input id score $;
datalines;
1 100
2 88
3 87
4 99
5 65
;
run;

data new_results;
set results;
numscore=input(score,8.);
drop score;
rename numscore=score;
run;
proc print; 
run;

/*Example 11: Same as above, but there are some non-digit values in the character variable that */
/*we want to convert to numeric.  We will end up with missing values for the new numeric variable for the */
/*character values that cannot be converted. The rest of the values will be converted correctly.  */
/*Look at the log to see the notes that have been generated.*/

data results;
input id score $;
datalines;
1 100
2 88
3 87
4 99
5 65
6 70-A
7 B
;
run;

data new_results;
set results;
numscore=input(score,8.);
drop score;
rename numscore=score;
run;
proc print; 
run;