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..
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
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.
Thank you Mr. Kurt for answer my question..
But, can I use that code in SAS Data Integration Studio?
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.
Thanks Mr.Kurt..
But it still doesn't work in DI Studio, and i got some errors..
@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.
I always used Data Validation for mapping the tables. I never use lookup. Or maybe next chance because I'm still learning.
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.
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
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, ^, ^=, |, ||, ~, ~=.
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).
Ok, thankyou Mr. Kurt for the explanation..
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
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 25. 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.