topic Re: Conditional Function in Advanced Expression in SAS Data Integration Studio in SAS Enterprise Guide

Conditional Function in Advanced Expression in SAS Data Integration Studio

wellylly — Tue, 18 Dec 2018 03:03:03 GMT

Hi....

I want to use advanced expression for mapping columns in SAS DI Studio. The data type of the target table is numeric, and the source table is character. I have to map them both with 4 conditions.
quarterly=1
semi annual=2
discounted=3
monthly=4

Could anyone help me to solve this case?? I even tried 'case when' and 'if' condition in the expression but it does not work.
Or maybe there's something wrong in my code 😞

Thanks before..

Re: Conditional Function in Advanced Expression in SAS DI

Kurt_Bremser — Mon, 17 Dec 2018 13:29:05 GMT

Use an informat:

proc format;
invalue myfmt
  "quarterly"=1
  "semi annual"=2
  "discounted"=3
  "monthly"=4
;
run;

data have;
input oldvar $20.;
cards;
quarterly
semi annual
discounted
monthly
;
run;

data want;
set have;
newvar = input(oldvar,myfmt.);
run;

proc print data=want noobs;
run;

Result:

oldvar         newvar

quarterly         1  
semi annual       2  
discounted        3  
monthly           4

As soon as you have the format defined, you can use it in the advanced expression of the query builder.

Re: Conditional Function in Advanced Expression in SAS DI

wellylly — Mon, 17 Dec 2018 13:48:16 GMT

Thank you Mr. Kurt for answer my question..

But, can I use that code in SAS Data Integration Studio?

Re: Conditional Function in Advanced Expression in SAS DI

Kurt_Bremser — Mon, 17 Dec 2018 14:47:51 GMT

input(oldvar,myfmt.);

can be used in the expression text.

I hope that DI Studio provides a task for creating informats; if not, put the proc format code into user-written code.

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

LinusH — Mon, 17 Dec 2018 15:51:31 GMT

No, creating informats doesn't come OOTB in Di Studio. Mapping data directly from lookup tables is favoured since it gives better linage using the built in impact analysis.

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

Kurt_Bremser — Tue, 18 Dec 2018 08:30:19 GMT

@LinusH wrote:
No, creating informats doesn't come OOTB in Di Studio. Mapping data directly from lookup tables is favoured since it gives better linage using the built in impact analysis.

Wow. Not even automatic creation from tables (which would facilitate lineage)?

What a poor cripple (IMHO), given how useful dynamically built custom formats/informats can be.

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

FredrikE — Tue, 18 Dec 2018 08:41:33 GMT

Hi!

I don't know the name of your source table variable, I call it [invar] 🙂

Try this case expression:

case

when [invar] = 'quarterly' then 1
when [invar] = 'semi annual' then 2
when [invar] = 'discounted' then 3
when [invar] = 'monthly' then 4

else .

end

//Fredrik

Re: Conditional Function in Advanced Expression in SAS DI

wellylly — Tue, 18 Dec 2018 08:50:01 GMT

Thanks Mr.Kurt..
But it still doesn't work in DI Studio, and i got some errors..

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

wellylly — Tue, 18 Dec 2018 08:54:26 GMT

Thanks Mr. Fredrike..

I've tried it, then i got this...

set DN_EIR.DN_OUTSTANDING_EIR;

5 where case

6 when Freq_Kupon = 'quarterly' then 1

____

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,

CONTAINS, EQ, GE, GT, IN, IS, LE, LIKE, LT, NE, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

Re: Conditional Function in Advanced Expression in SAS DI

Kurt_Bremser — Tue, 18 Dec 2018 08:55:09 GMT

Please enable us to follow Maxim 2.

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

Kurt_Bremser — Tue, 18 Dec 2018 08:57:15 GMT

The set startement points to a data step being used, but case/when/else/end is SQL syntax. And it is used in SQL to populate a column, not for where conditions (mostly).

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

wellylly — Thu, 20 Dec 2018 03:32:52 GMT

I always used Data Validation for mapping the tables. I never use lookup. Or maybe next chance because I'm still learning.

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

LinusH — Tue, 18 Dec 2018 10:18:37 GMT

Really, what?

Lineage is built around the traceability of data that flows in jobs/processes.

Also, ETL as such is built on this premise.

I guess that SAS <could> build a format generation transformation, but then they need to build some new kind of lineage between a new object type (catalogue entry format and informat),and I'm not convinced that it's worth it, since many use cases can be solved with other (and non-SAS specific) approaches. Like in this case I think a look up table combined with a Join or Lookup transformation would do the job.

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

FredrikE — Tue, 18 Dec 2018 14:22:09 GMT

Looks like the data validation transformation is a bit tricky to get everything correct...

I would try to use an extract transformation before and in that one create the case/when expression.

Test the extract until you are satisfied and then add the data validation.

//Freedrik

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

wellylly — Thu, 20 Dec 2018 03:29:47 GMT

Ok, thankyou Mr. Kurt for the explanation..

Re: Conditional Function in Advanced Expression in SAS Data Integration Studio

wellylly — Thu, 20 Dec 2018 03:41:15 GMT

Thank you so much Mr.Fredrike..
Finally, 'case when' statement is very helpful. I just forgot that the record is case sensitive. The real record is 'Quarterly' not 'quarterly' so that I got the error. So, I change the first alphabets to upper case, then it works 😄