BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
3 REPLIES 3
ChrisHemedinger
Community Manager
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
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
deleted_user
Not applicable
Thank you
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4799 views
  • 0 likes
  • 3 in conversation