32160 - How to Apply IF-THEN-ELSE Logic by Using SAS® Enterprise Guide

Sample 32160: How to Apply IF-THEN-ELSE Logic by Using SAS® Enterprise Guide

Overview

The Query Builder in SAS Enterprise Guide can be used to create new columns called Computed Columns.

The CASE statement can be used in Computed Columns when IF-THEN-ELSE logic is needed.

The purpose of this sample is to show a few of the many ways that you can utilize CASE syntax in SAS Enterprise Guide.

This sample requires only basic knowledge of SAS Enterprise Guide for opening data and building queries.

SAS Enterprise Guide 4.1.0.521 was used to create this sample.

The CLASS and SHOES data sets from the SASHELP library are used in this sample.

CLASS data

SHOES data

The first example includes detailed steps for creating a Computed Column in the Query Builder.

The remaining examples include only the CASE syntax that is required to achieve the particular goal.

Example 1.

This example creates a new character column called TEEN, and assigns a value of 'Pre-Teen' to the new TEEN column if the AGE column is less than or equal to 12, and assigns a value of 'Teen' to the new TEEN column if the AGE column is greater than or equal to 13 and less than or equal to 19, and assigns a value of 'Other' if the AGE column has a value greater than 19.

The If-Then-Else method that describes this is:

if age <= 12 then teen = 'Pre-Teen'

else if age >=13 and age <= 19 then teen = 'Teen'

else teen = 'Other'

Open the SASHELP.CLASS table into SAS Enterprise Guide.

SelectData→Filter and Query to open the Query Builder.

From the Query Builder, click Computed Columns to create a new column.

From the Computed Columns dialog box, click New, then Build Expression to open the Advanced Expression Editor.

From the Advanced Expression Editor, you can pull in a template for the CASE syntax by clicking Functions, then selecting Conditional, and then selecting one of the four templates from the panel on the right.

Alternatively, you can copy or type the CASE syntax.


case when CLASS.Age  <= 12 then 'Pre-Teen'
     when (CLASS.Age  >= 13) and  (CLASS.Age  <= 20) then 'Teen'
     else 'Other'
end

Click OK to close the Advanced Expression Editor dialog box.

Click Edit to change the name of the Computed Column, and then click Close.

Click Run to run the query and generate the results.

Example 2.

This example uses the SASHELP.CLASS data set and can be used to create a new column called TEEN. However, it uses a different method than Example 1 in order to assign a value of 'Teen' to the new TEEN column if the AGE column is greater than or equal to 13 and less than or equal to 19. If the value of AGE is outside the range, then the value of 'Other' is assigned to the column TEEN.


case when CLASS.Age in (13 14 15 16 17 18 19) then 'Teen'
     else 'Other'
end

Example 3.

This example uses the SASHELP.SHOES data set and can be used to create a new column that assigns a value of 'Men's Work Boot' when the value of the Product column is 'Boot'.


case when SHOES.Product = 'Boot' then "Men's Work Boot"
     else SHOES.Product
end

Example 4.

This example uses the SASHELP.SHOES data set and can be used to create a new column that assigns a value to the new column depending on the range of SHOES.Sales.


case when (SHOES.Sales between 0 and 49999.99)       then 'Needs work'
     when (SHOES.Sales between 50000 and 99999.99)   then 'Good work'
     when (SHOES.Sales between 100000 and 174999.99) then 'Really good work'
     when (SHOES.Sales >= 175000)                    then 'Ask for a raise!'
     else 'Further review is required'
end

Additional Documentation

For a list of available documentation for SAS Enterprise Guide, see http://support.sas.com/documentation/onlinedoc/guide/.

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.

Date Modified:	2008-07-02 10:49:54
Date Created:	2008-05-19 16:08:40

Product Family	Product	Host	Product Release		SAS Release
Product Family	Product	Host	Starting	Ending	Starting	Ending
SAS System	SAS Enterprise Guide	Microsoft Windows Server 2003 Datacenter Edition	4.1		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	4.1		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	4.1		9.1 TS1M3 SP4
		Microsoft Windows XP Professional	4.1		9.1 TS1M3 SP4

Support