60678 - Convert all character variables to numeric with macro CtoN

Sample 60678: Convert all character variables to numeric with macro CtoN

Convert all character variables to numeric with macro CtoN

Contents:

Purpose / History / Requirements / Usage / Details / Missing Values

PURPOSE:

The CtoN macro creates numeric variables from the specified (or all) character variables in a data set. The numeric values 1, 2, 3, ... are used in place of the original character values using any of four ordering schemes. The new numeric variables use the same names as the original character variables with optional prefix and suffix added. By default, only a suffix (_N) is added to each original variable name. Optionally, the macro creates and assigns formats to each of the new numeric variables labeling the new numeric values with the original character values.

HISTORY:

The version of the CtoN macro that you are using is displayed when you specify version (or any string) as the first argument. For example:

    %CtoN(version, <macro options>)

The CtoN macro always attempts to check for a later version of itself unless the nonewcheck option is specified. If it is unable to do this (such as if there is no active internet connection available), the macro will issue the following message:

    CtoN: Unable to check for newer version

The computations performed by the macro are not affected by the appearance of this message.

Version	Update Notes
1.1	Added datanote\|nodatanote and newcheck\|nonewcheck options.
1.0	Initial coding

REQUIREMENTS:

Base SAS^®.

USAGE:

Follow the instructions in the Downloads tab of this sample to save the CtoN macro definition. Replace the text within quotation marks in the following statement with the location of the CtoN macro definition file on your system. In your SAS program or in the SAS editor window, specify this statement to define the CtoN macro and make it available for use:

   %inc "<location of your file containing the CtoN macro>";

Following this statement, you can call the CtoN macro. See the Results tab for examples.

The following parameter is required when using the CtoN macro:

data=data-set-name: Specifies the data set containing the character variables to be converted.

The following parameters are optional:

var=list: Specifies a list of the names of the character variables to convert. Names must be separated by blanks. If omitted, all character variables are converted. Variable lists such as var1-var10 or abc--xyz are not allowed.
out=data-set-name: Specifies a name for the output data set that is created containing all of variables in the data= data set and the new numeric variables. By default, the output data set uses the name of the data= data set with _N appended. To overwrite the data= data set, use the same name in out=. If you specify data=mydata and omit out=, then the out= data set name is mydata_N. Note that a long data= name might be truncated to allow for the addition of _N so that it is a valid data set name.
prefix=string: Specifies a prefix to begin the names of all new numeric variables. By default, no prefix is added. prefix= is ignored if the replace option is specified.
suffix=string: Specifies a suffix to end the names of all new numeric variables. By default, suffix=_N. suffix= is ignored if the replace option is specified.
order=internal|formatted|data|freq: Specifies the ordering imposed on the distinct values found in each character variable. Once ordered in this way, numeric values 1, 2, 3, ... replace the character values in the new numeric variables. For the meaning of the different ordering types, see the description of the ORDER= option in the PROC FREQ statement. By default, order=internal.
options=replace|noreplace|format|noformat|datanote|nodatanote|newcheck|nonewcheck: replace specifies that the new numeric variables replace the original character variables in the out= data set. noreplace retains the original character variables as well as the new numeric variables. format specifies whether a format should be created and assigned to each new numeric variable. The created formats assign, as a label, the original character values to the new numeric values. With noformat, formats are not assigned to the numeric variables. nodatanote suppresses the notes produced when the out= data set is created. nonewcheck turns off the check for a new version of the macro. By default, options=replace format datanote newcheck.

DETAILS:

The specification %CtoN(data=a) creates data set a_N containing all of the numeric variables that were in data set a and numeric versions of all of the character variables that were in data set a. Each of the new numeric variables has the same name as the corresponding original character variable and has an associated format that assigns the original character values to the new numeric values, which are 1, 2, 3, ... .

To effectively replace all character variables in data set a with numeric variables as described above, specify %CtoN(data=a, out=a).

Note that a temporary data set containing all of the numeric replacement variables is created in the process. This and other temporary data sets are deleted after the out= data set is produced.

Any formats associated with the original character variables are not used in the out= data set. When not replacing the original character variables (via options=noreplace), the new numeric variables add the specified prefix and/or suffix to the original variable names. If the resulting variable name would be too long to be valid (exceeding 32 characters) then the original name is truncated as needed to allow for the addition of the prefix and/or suffix. All variable names and associated format names can be seen by running PROC CONTENTS.

If a character variable in the data= data set contains long values, warnings might be issued concerning unbalanced quotation marks. These warnings can be ignored.

MISSING VALUES:

If a value of a character variable is missing, the value of the corresponding numeric variable will also be missing.

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.

EXAMPLE 1

The following example uses a very small data set to illustrate the various macro options. The data step below creates a simple data set with one numeric variable, id, one character variable, a, and four observations. Notice that one value of a is a missing value.

data Ex1;
   infile datalines missover;
   input id:1. a:$1.;
   datalines; 
   1 b
   2 c
   3  
   4 a
   ;
proc print data=Ex1;
   run;

Obs	id	a
1	1	b
2	2	c
3	3
4	4	a

This statement makes the CtoN macro available in your current SAS session. Within the quotes, specify the location of the file containing your local copy of the CtoN macro. The statement only needs to be run once per SAS session.

%inc "<location of your file containing the CtoN macro>";

The first call of the macro detects all character variables in the data= data set, and creates an output data set named Ex1_N in which the one character variable found, a, is replaced with a numeric variable, also called a, that is formatted using the character values in the original variable. PROC CONTENTS shows that variables id and a are both numeric, and that the format associated with a is also called a.

%CtoN(data=Ex1)
proc contents data=Ex1_N;
   ods select variables;
   run;

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format
2	a	Num	8	A.
1	id	Num	8

Printing data set Ex1_N looks identical to the original data set, Ex1, because of the formatting. Notice that the missing value in the original character variable is also missing in the new numeric variable.

proc print data=Ex1_N;
   run;

Obs	id	a
1	1	b
2	2	c
3	3	.
4	4	a

Using a FORMAT statement with no format specified removes the formatting so that the numeric coding of a is visible. Notice that the values 1, 2, 3 are assigned to the original values in sorted order, which is the default.

proc print data=Ex1_N;
   format a;
   run;

Obs	id	a
1	1	2
2	2	3
3	3	.
4	4	1

The next macro call shows the effects of the noreplace and noformat options. With noreplace, the original character variable is retained along with a new numeric variable named a_N. The noformat option prevents the assignment of a format to the numeric variable as can be seen in the PROC PRINT results. PROC CONTENTS shows there are now three variables in data set Ex1_N and no formats are associated.

%CtoN(data=Ex1, options=noreplace noformat)
proc print data=Ex1_N;
   run;
proc contents data=Ex1_N;
   ods select variables;
   run;

Obs	id	a	a_N
1	1	b	2
2	2	c	3
3	3		.
4	4	a	1

Alphabetic List of Variables and Attributes
#	Variable	Type	Len
2	a	Char	1
3	a_N	Num	8
1	id	Num	8

Next, the order= option is used. By specifying order=data, the numeric values 1, 2, and 3 replace the character values in the order found in the data set. That is, the first value found, 'b', is assigned value 1. The second value, 'c', is assigned 2, and the third nonmissing value, 'a', is assigned 3. All variables are again retained by the noreplace option and formatting is prevented by the noformat option so that a simple PROC PRINT shows the change in ordering as compared to the default (order=internal).

%CtoN(data=Ex1, order=data, options=noreplace noformat)
proc print data=Ex1_N;
   run;

Obs	id	a	a_N
1	1	b	1
2	2	c	2
3	3		.
4	4	a	3

Finally, the prefix= and suffix= options are used to show how the new variable names can be customized. The following macro call adds 'num_' at the beginning of all new numeric variable names in the output data set, new. Since the default is suffix=_N, specifying suffix= prevents any suffix characters from being added to the ends of the variable names. Note that when the replace option is in effect, the prefix= and suffix= options are ignored.

%CtoN(data=Ex1, prefix=num_, suffix=, out=new, options=noreplace noformat)
proc print data=new;
   run;

Obs	id	a	num_a
1	1	b	2
2	2	c	3
3	3		.
4	4	a	1

EXAMPLE 2: Converting multiple character variables

A single call of the CtoN macro can create numeric variables for each of many character variables in a data set. The sashelp.class data set has two character variables, Name and Sex, along with several numeric variables as shown by PROC CONTENTS.

proc contents data=sashelp.class;
   ods select variables;
   run;

Alphabetic List of Variables and Attributes
#	Variable	Type	Len
3	Age	Num	8
4	Height	Num	8
1	Name	Char	8
2	Sex	Char	1
5	Weight	Num	8

This call of the macro processes all character variables and creates a new data set, named newclass, which contains numeric replacements of the character variables. As in the example above, printing the data set using the formats that are assigned by default looks the same as printing the original data set. By removing all formats with a FORMAT statement, the numeric coding of the new variables can be seen.

%CtoN(data=sashelp.class, out=newclass)
proc print data=newclass;
   run;
proc print data=newclass;
   format _all_;
   run;

Obs	Age	Height	Weight	Name	Sex
1	14	69.0	112.5	Alfred	M
2	13	56.5	84.0	Alice	F
3	13	65.3	98.0	Barbara	F
4	14	62.8	102.5	Carol	F
5	14	63.5	102.5	Henry	M
6	12	57.3	83.0	James	M
7	12	59.8	84.5	Jane	F
8	15	62.5	112.5	Janet	F
9	13	62.5	84.0	Jeffrey	M
10	12	59.0	99.5	John	M
11	11	51.3	50.5	Joyce	F
12	14	64.3	90.0	Judy	F
13	12	56.3	77.0	Louise	F
14	15	66.5	112.0	Mary	F
15	16	72.0	150.0	Philip	M
16	12	64.8	128.0	Robert	M
17	15	67.0	133.0	Ronald	M
18	11	57.5	85.0	Thomas	M
19	15	66.5	112.0	William	M

Obs	Age	Height	Weight	Name	Sex
1	14	69.0	112.5	1	2
2	13	56.5	84.0	2	1
3	13	65.3	98.0	3	1
4	14	62.8	102.5	4	1
5	14	63.5	102.5	5	2
6	12	57.3	83.0	6	2
7	12	59.8	84.5	7	1
8	15	62.5	112.5	8	1
9	13	62.5	84.0	9	2
10	12	59.0	99.5	10	2
11	11	51.3	50.5	11	1
12	14	64.3	90.0	12	1
13	12	56.3	77.0	13	1
14	15	66.5	112.0	14	1
15	16	72.0	150.0	15	2
16	12	64.8	128.0	16	2
17	15	67.0	133.0	17	2
18	11	57.5	85.0	18	2
19	15	66.5	112.0	19	2

Specify the var= option if only a subset of the existing character variables are to be replaced. In this call to the macro, only the Sex variable is replaced. Preventing the association of a format with the noformat option allows a basic PROC PRINT step to show the numeric coding. Additionally, the numeric values (1, 2) are assigned to the values of Sex in the order seen in the data set (via order=data), resulting in Sex='M' now being coded 1 rather than 2.

%CtoN(data=sashelp.class, var=Sex, order=data, out=newclass, options=noformat)
proc print data=newclass;
   run;

Obs	Name	Age	Height	Weight	Sex
1	Alfred	14	69.0	112.5	1
2	Alice	13	56.5	84.0	2
3	Barbara	13	65.3	98.0	2
4	Carol	14	62.8	102.5	2
5	Henry	14	63.5	102.5	1
6	James	12	57.3	83.0	1
7	Jane	12	59.8	84.5	2
8	Janet	15	62.5	112.5	2
9	Jeffrey	13	62.5	84.0	1
10	John	12	59.0	99.5	1
11	Joyce	11	51.3	50.5	2
12	Judy	14	64.3	90.0	2
13	Louise	12	56.3	77.0	2
14	Mary	15	66.5	112.0	2
15	Philip	16	72.0	150.0	1
16	Robert	12	64.8	128.0	1
17	Ronald	15	67.0	133.0	1
18	Thomas	11	57.5	85.0	1
19	William	15	66.5	112.0	1

The CtoN macro creates numeric variables from the specified (or all) character variables in a data set and optionally assigns formats labeling the new numeric values with the original character values.

Type:	Sample
Topic:	Data Management ==> Data Sources ==> SAS Data Sets/Tables Data Management ==> Manipulation and Transformation

Date Modified:	2021-09-17 13:30:57
Date Created:	2017-06-23 16:08:52

Operating System and Release Information

Product Family	Product	Host	SAS Release
Product Family	Product	Host	Starting	Ending
SAS System	Base SAS	z/OS
		z/OS 64-bit
		OpenVMS VAX
		Macintosh
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows XP 64-bit Edition
		Microsoft® Windows® for x64
		OS/2
		Microsoft Windows 8 Enterprise 32-bit
		Microsoft Windows 8 Enterprise x64
		Microsoft Windows 8 Pro 32-bit
		Microsoft Windows 8 Pro x64
		Microsoft Windows 8.1 Enterprise 32-bit
		Microsoft Windows 8.1 Enterprise x64
		Microsoft Windows 8.1 Pro 32-bit
		Microsoft Windows 8.1 Pro x64
		Microsoft Windows 10
		Microsoft Windows 95/98
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Professional
		Microsoft Windows NT Workstation
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows Server 2003 for x64
		Microsoft Windows Server 2008
		Microsoft Windows Server 2008 R2
		Microsoft Windows Server 2008 for x64
		Microsoft Windows Server 2012 Datacenter
		Microsoft Windows Server 2012 R2 Datacenter
		Microsoft Windows Server 2012 R2 Std
		Microsoft Windows Server 2012 Std
		Microsoft Windows XP Professional
		Windows 7 Enterprise 32 bit
		Windows 7 Enterprise x64
		Windows 7 Home Premium 32 bit
		Windows 7 Home Premium x64
		Windows 7 Professional 32 bit
		Windows 7 Professional x64
		Windows 7 Ultimate 32 bit
		Windows 7 Ultimate x64
		Windows Millennium Edition (Me)
		Windows Vista
		Windows Vista for x64
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		ABI+ for Intel Architecture
		AIX
		HP-UX
		HP-UX IPF
		IRIX
		Linux
		Linux for x64
		Linux on Itanium
		OpenVMS Alpha
		OpenVMS on HP Integrity
		Solaris
		Solaris for x64
		Tru64 UNIX

Support

Sample 60678: Convert all character variables to numeric with macro CtoN

Convert all character variables to numeric with macro CtoN

Operating System and Release Information