If..Then..Else Statements

[deleted_user]

I hope this is reaching you in the best of health and spirits

I am new to EG and still trying to feel my way around. Is there a way to expand the use of the compute function in the query task? I would like to define a column's value base of data from tw different fields:

Example
If Sub-Market Code = 500 and Market is = "Test" then name "Test Group"

If this is not possible with the compute function in the query task can you advice me how to do an then..else statement in EG

Thanks in advance

[ChrisHemedinger]

You can get this done with the CASE statement in the expression builder in the query builder. If you are using the query to pull a subset of the data, this has the advantage of creating the new column as it filters the data.

Create a calculated column, using an expression like this:

[pre]
CASE
WHEN (SOURCE.sub_market_code = 500 and SOURCE.market = "Test")
THEN "Test Group"
END
[/pre]

EG will generate the "as Name" suffix that creates a new column named Name or whatever you choose.

That works within the proc sql that EG generates.

If want a code approach and don't mind making another pass through the data, you could use a simple DATA step:

[pre]
data SOURCE;
set SOURCE;
length name $ 20;
if (DATA.sub_market_code = 500 and DATA.market = "Test") then
name="Test Group";
run;
[/pre]

Chris

[deleted_user]

Thank you

[Cynthia_sas]

Hi:
In the GUI interface, when you create a computed column, EG gives you an expression editor where you can enter your "condition". The "new computed column" is entered through the popup window that's associated with the expression. EG is actually building an SQL query behind the scenes and creating PROC SQL code.

The way you create columns conditionally in PROC SQL is with a CASE clause. For example:
[pre]
proc sql;
create table work.newclass as
select a.Name,
a.Sex,
a.Age,
(case
when a.sex = 'F' and a.age lt 13 then 'One'
when a.sex = 'F' and a.age ge 13 then 'Two'
when a.sex = 'M' and a.age lt 14 then 'Three'
when a.sex = 'M' and a.age ge 14 then 'Four'
else 'None'
end) as newvar
from sashelp.class as a;
quit;

proc print data=newclass;
title 'SQL CASE Example ';
run;
[/pre]


So, if you build your query with the EG expression editor, I believe (and you might want to check with Tech Support on this) that you have to enter just the (CASE/END) in the expression editor. EG adds the "AS Calculation1" or whatever you call the new column in the computed column window.

In your example, the CASE clause would be something like:
[pre]
(case
when Sub_Market = 500 and Market = 'Test' then "Test Group"
when Sub_Market = 250 and Market = 'Prod' then "Not Test"
... more conditions ...
else "Other"
end)
[/pre]

You normally have to use the column NAMES and not the column LABELS in your expressions. The string "Sub-Market Code" is probably the label for a column and is not a column name because SAS columns cannot contain spaces or dashes. I don't know what the variable name is, but I coded it as Sub_Market in the code snippet above.

If you have not already found them, there are 2 excellent books on using SAS EG:
The Little SAS Book for Enterprise Guide 4.1: For Enterprise Guide 4.1
By Susan J. Slaughter, Lora D. Delwiche
and
SAS for Dummies
by Stephen McDaniel, Chris Hemedinger

The second book goes into more detail on the SAS Business Intelligence Platform, so it covers more topics than the first book. I'd recommend checking them both out and deciding which one better fits your needs.

cynthia