Can someone please tell me why the following code doesn't work as an expression in the mapping tab of a DI Studio transformation? case EMAIL   when not contains "@" then NULL   when contains "æ" then NULL   when contains "ø" then NULL   when contains "å" then NULL   else EMAIL end I get this error message: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, , =, , =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. Thanks. ... View more

I have a macrovariable called &date_today. It has the date9 format, and appears as 23NOV2012. I need to set up a second macrovariable called &date_yesterday, using a %let statement. It should be based on &date_today but subtract one day from it, so that it becomes 22NOV2012. I've spent two hours looking for a way to accomplish this, and tried numerous approaches, yet it never seems to work. It's terribly annoying, because it's such a simple thing to accomplish in a DATA step. This is the very simple gist of what I want to do: %let &date_yesterday = &date_now -1. Can anyone please help me resolve this and end my frustrations? P.S. I know how to set up &date_yesterday independently of the &date_today, using INTNX, but that's not what I need. The new variable must be the equivalent of "&date_today minus one day". ... View more

I have a macrovariable called &filterdate_start. It's DATETIME20 and has the value 26NOV2012:00:00:00. I need a new macrovariable called &filterdate_end to equal &filterdate_start + 2 days, with the value 28NOV2012:00:00:00. What's the most practical way to add 2 days to a DATETIME20 value? ... View more

I've created a DI Studio job that requires complex date variables in order to function. All the date variables are calculated in a data step in the precode of an Extract transformation, and show up with correct values in the log. However, I just learned that the precode data step variables aren't accessible from the Extract's where-statement. What's the best way of making the Extract's precode data step variables available for reference in the Extract's where-statement? I attempted putting %let in front of each variable to no avail, so I guess they're all local to the data step. Thanks for your time. ... View more

I have a bit of a complex problem (at least from my point of view) that I'm hoping for some assistance with. It's regarding a DI Studio job that's used for sending out alerts to our consultants. As you're all probably aware, weekday("&sysdate"d) returns a single numeric digit indicating the weekday. 1=Sun, 2=Mon, 3=Tue, etc. My job is scheduled to run on each business day, and the first thing it does is to determine the weekday. If it's Monday (2), it sends alerts from Friday (6). If it's Tuesday (3), it sends alerts from Monday (2). If it's Wednesday (4), it sends alerts from Tuesday (3). If it's Thursday (5), it sends alerts from Wednesday(4). If it's Friday (6), it sends alerts from Thursday(5). This has worked perfectly. However, now I need to implement an adjustable parameter (&days_delayed) for delaying the alerts. In example, if &days_delayed is set to 2, and the job is executed on a Thursday , it should send the alerts from Monday instead of Wednesday. The purpose of the delay is to give postal mail time to reach the customers before our consultants receive the alert and call them up. Because of this, the delay should avoid counting Saturdays and Sundays, because the postal mail doesn't make much progress on those days. So far I've implemented the delay parameter so that if it's set to 2, and the job is executed on a Thursday , it sends the alerts from Monday instead of Wednesday. However, if &days_delayed was set to 3 instead, it would try to send the alerts from Sunday. In this example I would want it to send alerts from Friday, because that's the 4th business day back from Thursday. If &days_delayed was a static value I could solve this by means of using precode like "if business_day=1 then business_day=6" etc., but I can't figure out how to do it when &days_delayed is supposed to be an easily adjustable value. I'm probably overlooking a simple solution, though. Any guidance would be greatly appreciated. ... View more

If I have the number "15748645879", and want to add a space after the 6th number so that it becomes "157486 45879", how do I accomplish this in a DI Studio transformation expression (or format)? Thanks. ... View more