26099 - Creating Columns Using Functions in the Expression Editor of SAS Enterprise Guide

Sample 26099: Creating Columns Using Functions in the Expression Editor of SAS Enterprise Guide

Creating Columns Using Functions in the Expression Editor

SAS Enterprise Guide has many built-in functions you can use to build expressions when creating new columns. A function takes a value and turns it into another related value. For example, the MONTH function will take a date and return just the month. The LOG function will return the natural log of a number. There are many functions to choose from in over 20 different categories including: arithmetic, character, date and time, descriptive statistics, financial, trigonometric, and truncation. To create new columns using functions, you use the Query Builder.

Here is a portion of the Bookings Excel file which has been converted to a SAS data table. The Deposit_Date column is a datetime value.

Data screen shot

To create a column that just has date values, you can use the DATEPART function. To open the Query Builder, click the data icon in the Project Explorer or Project Designer to make it active, and then select

Data ► Filter and Query

from the menu bar. The Query Builder opens with the Select Data tab on top. Select the columns for the query, in this case all of them.

Query builder screen shot

Creating a new column
To create a new column, click the Computed Columns button in the Query Builder window. This opens the Computed Columns window. Click New and select Build Expression. This opens the Advanced Expression Editor window where you specify the expression to use for the new column.

Computed columns screen shot

Choosing a function
Click the Functions tab to list all available functions. These functions are grouped by category. Click a category, and all the functions in that category will be listed in the box on the right under Functions. To insert a function into an expression, click the function name, and then click Add to Expression. In this example, the help text at the bottom of the Functions tab tells you that the DATEPART function extracts the date (the number of days since January 1, 1960) from a datetime value (the number of seconds since midnight on January 1,1960).

Advanced expressions editor screen shot

Defining arguments for functions
Most functions take some sort of argument. When the function is inserted into the expression, a placeholder for the argument appears in the expression. You must replace the placeholder in the function with a valid argument. If the function calls for a character value, that value can be a column of type character, or a character string enclosed in quotes. If the function calls for a numeric value, that value can be a column of type numeric, or a number.

Click the Data tab to insert column names or values into the expression. Click the name of the column to insert into the function from the Variable values list, and then highlight the placeholder in the function. Click Add to Expression and the placeholder will be replaced by the column you selected, in this case the Deposit_Date column from the Bookings table.

Editor screen shot

Here is what the expression looks like after replacing the placeholder.

Editor screen shot

When you are satisfied with your expression, click OK to return to the Computed Columns window where you can rename the column if you like. Click Close in the Computed Columns window, and then click Run in the Query Builder.

Computed columns screen shot

Results
Notice here how the values for the new column, DateOfDeposit, are simple numbers. These numbers are dates represented as the number of days since January 1, 1960. To display the numbers as readable dates, give the column a date display format in the Properties window for the column.

Results screen shot

About the Authors
In 1992, Susan Slaughter and Lora Delwiche teamed up to write The Little SAS Book: A Primer, now in its third edition, which quickly became a popular item on SAS users' bookshelves. They have presented numerous papers at local, regional, and international SAS users group meetings. Their interactions with other SAS users have inspired them to write books that are both informative and enjoyable to use. Susan currently works as a consultant through her company, Avocet Solutions, while Lora works in research at the University of California, Davis.

Their books are available from the online bookstore.

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.

Type:	Sample
Topic:	Non SAS Authors ==> Lora Delwiche Non SAS Authors ==> Susan Slaughter Internal Administration ==> BBU

Date Modified:	2006-11-08 03:03:00
Date Created:	2006-11-08 03:03:00

Product Family	Product	Host	SAS Release
Product Family	Product	Host	Starting	Ending
SAS System	SAS Enterprise Guide	All	9 TS M0	9.1 TS1M3
SAS System	Base SAS	All	9 TS M0	9.1 TS1M3

Support

Sample 26099: Creating Columns Using Functions in the Expression Editor of SAS Enterprise Guide

Operating System and Release Information