Hi Everyone,
Currently I am working in SAS Enterprise Guide. I'm trying to create a new column from two columns using advanced expression. The idea is this: There is a column named 'Department' and one named 'Project'. If in 'Project' the characters contain 'XYZ', then the value in 'Department' needs to be adjusted to 'ABC'. If, in 'Project' the characters do not contain 'XYZ', we need to keep using the value already present in 'Department'. So basically I'm trying to replace a value in 'Department', based on the values in 'Project'. I'm trying to use FIND within a IF ELSE statement, but so far it's not working. Searches online are not really helping.
Does anyone have a suggestion as to how I can achieve this result? Many thanks!
After selecting Advanced Expression click Next then type this into the Enter an Expression box
case when find(project,'XYZ') ne 0 then 'ABC' else Department end
Not having the data you're working with, I made some up. Give this a try:
data have;
input department $ project & $15.;
datalines;
dept_1 QWERTY
dept_2 WXYZ
dept_3 123-XYZ
dept_4 not capital xyz
;
run;
data want;
set have;
if find(project, 'XYZ') ge 1
then department = 'ABC';
run;
See this data step code:
data have;
input project :$3. department :$3.;
datalines;
PRQ AAA
XYZ BBB
;
data want;
set have;
if find(project,'XYZ') then department = 'ABC';
run;
With SQL, it looks like this:
proc sql;
create table want as
select
project,
case
when find(project,'XYZ') then 'ABC'
else department
end as department
from have
;
quit;
If you need to create an expression in EG's Query Builder, you need to use SQL syntax (case-when-then-else-end)
After selecting Advanced Expression click Next then type this into the Enter an Expression box
case when find(project,'XYZ') ne 0 then 'ABC' else Department end
Thanks, this turned out to be the proper solution!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: