SAS EG Computing new column with advanced expression while using FIND function

[Margot89]

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!

[mklangley]

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;

[Kurt_Bremser]

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)

[sustagens · Accepted solution]

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

[Margot89]

Thanks, this turned out to be the proper solution!