Converting Variable Types in SAS® Enterprise Guide®


Overview

SAS Enterprise Guide provides easy access to data sources through a graphical interface, which means that 99% of the time you can work in SAS without knowing the SAS programming language. However, if you want to manipulate data, such as changing date values or parsing a character string, then you will need to employ some SAS programming knowledge in order to achieve your goals.

One very common data manipulation is converting a variable type from either character to numeric or from numeric to character. This conversion is done by using the PUT and INPUT functions.

SAS does not allow you to change the type of a variable that is already defined, so a new variable must be created. SAS Enterprise Guide enables you to create new variables (computed columns) by using the Advanced Expression builder within the Query Builder. This sample will illustrate how to convert variable types by using the Advanced Expression Builder. You will perform the following tasks:

  1. Convert a character date variable into a numeric SAS date variable.
  2. Convert a numeric value to a character string, adding leading zeros to the value. (Leading zeros are not retained in a numeric value.)
  3. Apply a date format to the new SAS date variable.

Create the Sample Data

To create the sample data, you can select File ► New ► Code and submit the DATA step code shown below in the SAS Enterprise Guide code node.

DATA SAMPLE; 
INPUT DATE :$10.  NUM; 
CARDS;
1/10/2006 123 
4/24/2005 456 
7/4/2007 789 
; 
RUN;

After you submit the code, the table opens automatically.

Launch the Query Builder

  1. Display the Process Flow window, right-click your sample data set, and select Query Builder. 
  2. Click Computed Columns and then select New ► Build Expression to open the Advanced Expression Editor dialog box.

Convert a Character Value to a Numeric Value

Working with the character date value first, you will use the INPUT function to create a new numeric SAS variable. The INPUT function converts character strings to numeric values, using the appropriate informat that corresponds to the character string.

For example, if you had a value of 08MAR2000, you would use the DATEw. informat to read the value. For the date values in the sample data set, you need to use the MMDDYYw. informat. The structure of the expression looks similar to the following:

INPUT(date,mmddyy10.)

The first argument to the INPUT function is the variable that you want to convert. The second argument is the appropriate informat and width.

  1. Using the Advanced Expression Editor, click the Functions tab. In the Category list, scroll down to Special, select INPUT(character) from the Functions list, and then click Add to Expression.
  2. Click the Data tab and select the DATE variable. Then, select the <charValue> placeholder in the Expression text box and click Add to Expression. You cannot get a list of available informats through the Advanced Expression Editor, so you must select the second placeholder <value> and enter MMDDYY10.. The expression should now look similar to the following:
  3. Click OK to exit the Advanced Expression editor and then rename the variable from Calculation1 to a name of your choice. Click Close to return to the Query Builder where you should now see the calculated column in the query builder.

Convert a Numeric Value to a Character Value

You will now perform similar tasks in order to convert the numeric value to a character value, except you will use the PUT function instead of the INPUT function. The PUT function converts a numeric variable into a character string, using the appropriate format that corresponds to the numeric value. The formatted value is then stored as a character string.

Because you want to create a character string with three leading zeroes, you will use the Zw. numeric format. The structure of the expression looks like this:

PUT(num,Z6.)

The first argument to the PUT function is the variable that you want to convert. The second argument is the appropriate format and width.

  1. In the Query Builder, click Computed Columns again, and then select New ► Build Expression to open the Advanced Expression Editor dialog box.
  2. Using the Advanced Expression Editor, click the Functions tab. In the Category list, scroll down to Special, select PUT(numeric) from the Functions list, and then click Add to Expression.
  3. Click the Data tab and select the NUM variable. Then, select the <numValue> placeholder in the Expression text box and click Add to Expression. You cannot get a list of available informats through the Advanced Expression Editor, so you must select the second placeholder <value> and enter Z6.. The expression should now look similar to the following:
  4. Click OK to exit the Advanced Expression editor, and then rename the variable from Calculation1 to a name of your choice. Click Close to return to the Query Builder where you should now see the calculated column in the query builder.

Run the Query

In the Query Builder, select the variables that you want to use in the query, including the two new variables. Click Run. You should see the new variables in the resulting data set.

Add a Format to the SAS Date

If you are converting SAS dates, be aware that a SAS date value is a number equal to the number of days since January 1, 1960. This is what the INPUT function has created from the character date string: an unformatted SAS date value. To display the value as a recognizable date, you must apply a date format to the variable.

You should see the newly formatted values in the resulting data set.

  1. On the Select Data tab in the Query Builder, select the new date variable, and then click  (Properties) to the right. This will open the Properties dialog box for the date variable.
  2. By default, there is no format applied to the variable. Click Change (to the left of the Format field) to open the Formats dialog box.
  3. From the Categories list, select Date, and then select the date format and width you want to apply to the variable.
  4. Click OK to return to the Query Builder and then click Run to re-run the query.

Additional Documentation

For more information about using SAS Enterprise Guide, see the SAS Enterprise Guide documentation page.

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.