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.
- 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.
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
|
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.