topic Re: SAS EG Computing new column with advanced expression while using FIND function in SAS Enterprise Guide

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

Margot89 — Wed, 24 Jun 2020 13:37:05 GMT

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!

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

mklangley — Wed, 24 Jun 2020 14:48:34 GMT

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;

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

Kurt_Bremser — Wed, 24 Jun 2020 14:51:38 GMT

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)

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

sustagens — Thu, 25 Jun 2020 01:24:12 GMT

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

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

Margot89 — Thu, 25 Jun 2020 09:54:52 GMT

Thanks, this turned out to be the proper solution!