Conditional Function in Advanced Expression in SAS Data Integration Studio

[wellylly]

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..

[Kurt_Bremser]

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.

[wellylly]

Thank you Mr. Kurt for answer my question.. 

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

 

[Kurt_Bremser]

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.

[wellylly]

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

[Kurt_Bremser]

Please enable us to follow Maxim 2.

[LinusH]

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.

[Kurt_Bremser]

---

@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.

[wellylly]

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

[LinusH]

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.

[FredrikE · Accepted solution]

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

[wellylly]

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

               ____

               22

               76

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, ^, ^=, |, ||, ~, ~=.  

[Kurt_Bremser]

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).

[wellylly]

Ok, thankyou Mr. Kurt for the explanation..

[FredrikE]

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