26098 - Creating Columns Using Mathematical Operators in the Expression Editor of SAS Enterprise Guide

Sample 26098: Creating Columns Using Mathematical Operators in the Expression Editor of SAS Enterprise Guide

Creating Columns Using Mathematical Operators in the Expression Editor

Sometimes you need to create a new column based on data values in other columns. To do this, add a computed column in the Query Builder, and then use the Advanced Expression Editor to specify the expression to use for the values in the new column. This example uses the Eruptions data table which contains start and end dates for volcano eruptions around the world.

Data grid screen shot

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, they are Volcano, StartDate, and EndDate.

Query builder screen shot

Creating a new column
To create a new column, click the Computed Columns button located near the top of the 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

Building the expression
The Advanced Expression Editor has a large box at the top of the window. You can simply type the expression in this box, or you can let SAS Enterprise Guide help you build the expression. The bottom part of the window has a tab for Data and a tab for Functions. The Available variables box of the Data tab shows all the data tables and columns in the query. When you click an item in the Available variables list, all the possible values of the item are listed in the Variable values box on the right. If you click a data table, as in this example, all the columns in the data table appear in the Variable values box. If you click a column name in the Available variables list, all unique values for that column in the data table appear in the Variable values box. To add items to the expression you are building, click the item in the Variable values box, and then click Add to Expression. You can use the various operator buttons that appear below the Expression text box to build your expression. In this example, you want the length of the eruption in days. Because the StartDate and EndDate are both SAS date values (the number of days since January 1, 1960), you can simply subtract the start date from the end date and add one.

Expression editor screen shot Click OK to return to the Computed Columns window. The column is given the name Calculation1. To rename the column, click Rename and enter a new name, LengthOfEruption in this example. Click Close and then click Run in the Query Builder window to run the query.

alt="Computed columns screen shot">

Results
The Query Builder creates a new SAS data table that contains the new column. The new data table is given a name starting with the letters QUERY and is stored in a default location. You can change the location or name of the data table using the Change button in the Query Builder window. Here are the results of the query which now include the new column LengthOfEruption.

Output data grid 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 26098: Creating Columns Using Mathematical Operators in the Expression Editor of SAS Enterprise Guide

Operating System and Release Information