Sample 38261: Using conditional logic in the SAS® Enterprise Guide 4.2 Query Builder
Overview
When you want to conditionally process a data set (such as creating a new variable based on a condition), you typically think of using IF-THEN logic, but IF-THEN logic is only valid in DATA step. In SAS Enterprise Guide, the Query Builder uses PROC SQL behind the scenes. In PROC SQL, the CASE statement is used to do conditional processing.
In SAS Enterprise Guide 4.2, the method of using the CASE statement has changed. This sample introduces you to those changes by explaining how to create a new variable named TYPE that is based on the value of a variable named CODE. Specifically, if CODE=1, then TYPE is equal to 'Fruit'. If CODE=2, then TYPE is equal to 'Vegetable'.
Note: For an example of using the CASE statement in SAS Enterprise Guide 4.1, see Sample 32160.
About the sample data
Here is the SAS code that is used to create the sample data set.
data produce;
input name $ code;
cards;
banana 1
apple 1
lettuce 2
carrot 2
pepper 2
orange 1
potato 2
corn 2
grape 1
mango 1
;
run;
|
Using the CASE statement to create a new variable
- Select File ► New ► Program, and then submit the sample code to create the sample data set.
- In the Process Flow window, right-click on the PRODUCE data set and select Query Builder.
- In the Query Builder, click Computed Columns, and then click New.
- In the New Computed Column wizard, select the Recoded Column option, and then click Next.
- On page 2 of the wizard, select the column that will be the basis for your new column. For this sample, select the CODE column, and then click Next.
- On page 3 of the wizard, select the values you want to use in place of the current values. The new values will be written to a new variable instead of overwriting the values of the current variable. Click Add, and then select the Replace Condition tab.
![image label](addl/fusion_38261_1_specify_replacement.jpg)
- In the Value box, specify the current value of the variable. If you aren't sure of the current value, click the drop-down arrow to open a new dialog box. On the Values tab, click Get Values. The Values tab should be populated with available values. Select the value you want to use. For this sample, select 1. In the box labeled With this value, specify the new value. For this sample, enter Fruit. Make sure that the Enclose this value in quotes option is selected.
![image label](addl/fusion_38261_5_replacement_2.jpg)
- Click OK, which takes you back to page 3 of the wizard.
![image label](addl/fusion_38261_2_computed_col_part_1.jpg)
- Click Add and repeat steps 6 through 8 to replace the variable value 2 with the value Vegetable.
- Still on page 3 of the wizard, beneath Other values on the right, select Specify a value. In the box, enter other in the box, and then select Enclose this value in quotes. If the variable CODE contains any value besides 1 or 2, then the new value will be 'other'. Select Character as the Column type. Click Next.
![image label](addl/fusion_38261_3_computed_col_part_2.jpg)
- On page 4 of the wizard, you give your new variable a name and alias. For this sample, the name is Type and the alias is Type of Produce. In the Expression box, you can see the expression that SAS Enterprise Guide built. In this sample, the expression is as follows:
CASE
WHEN t1.code = 1 THEN 'Fruit'
WHEN t1.code = 2 THEN 'Vegetable'
ELSE 'other'
END
|
![image label](addl/fusion_38261_6_step_4_of_5.jpg)
Click Next.
- Page 5 of the wizard shows a summary of properties about the variable that you are creating. If the properties look fine, click Finish, and then Close. The new variable should be added to the Select Data tab in the Query Builder. Click Run to run the query and add the new variable to the data set. In the following figure, NAME and CODE are also included in the query.
![image label](addl/fusion_38261_7_final_result.jpg)
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.
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.
In SAS Enterprise Guide 4.2, the method of using the CASE statement has changed. This sample introduces you to those changes by explaining how to create a new variable based on a condition.
Date Modified: | 2010-01-06 14:09:24 |
Date Created: | 2009-12-23 14:12:56 |
Operating System and Release Information
SAS System | SAS Enterprise Guide | Microsoft® Windows® for x64 | 4.2 | | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Datacenter Edition | 4.2 | | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Enterprise Edition | 4.2 | | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Standard Edition | 4.2 | | 9.2 TS2M0 | |
Microsoft Windows XP Professional | 4.2 | | 9.2 TS2M0 | |
Windows Vista | 4.2 | | 9.2 TS2M0 | |