About TurnTheBacon

TurnTheBacon · ‎06-10-2013

Worked like a charm, many thanks!

TurnTheBacon · ‎06-10-2013

Thanks, but if I simply specify DATE9 as the target table column format, all the values show up as " ********* ". Incompatible date formats, I think. Incidentally, if I use the expression datepart(due_dt) and the DATE9 format in the target table, all date values display as "20AUG1960" (correct format, wrong values). EDIT: If I force it to have the 8.0 format, the values still appear as "20130531".

TurnTheBacon · ‎06-07-2013

I'm working in DI Studio, and have trouble changing the format of a numeric variable. My input table has a numeric variable with the format yyyymmdd. Value example: 20130531. I need to convert it to the DATE9. format, so that it displays as 31MAY2013. What kind of expression can I use for this? I tried using this: input(put(DUE_DT, yyyymmdd.),date9.) However, the values only become blank/null. Thanks for your time!

TurnTheBacon · ‎05-08-2013

Thanks, I was able to do it using substr and translate.

TurnTheBacon · ‎05-07-2013

Imagine that you have a folder with the following reports: Commissions GW 0413 Ohio Commissions GW 0413 Indiana Commissions GW 0413 Minnesota Commissions GW 0413 Illinois Commissions GW 0413 Pennsylvania Commissions GW 0413 Colorado You want SAS to read the file names starting from "GW", and you want to turn the spaces into underscores. So, when read into SAS, the strings should look like this: reportname GW_0413_Ohio GW_0413_Indiana GW_0413_Minnesota GW_0413_Illinois GW_0413_Pennsylvania GW_0413_Colorado I think this can be done by means of the SCAN function, but I can't seem to make it work. I'd appreciate any advice. Thanks for your time.

TurnTheBacon · ‎04-30-2013

Hello, When a job in SAS DI Studio writes to a path that's over 200 characters long, the filename is cut off. So, in example, a CSV-file might end up as "/SASEnvironment/etc/etc/etc/filename.cs" and be considered a C# file. I believe this is due to a Windows limitation. Is there any way around this other than shortening folder and file names? Thanks.

TurnTheBacon · ‎02-07-2013

Thanks. Yes, that's correct. I know how to do it in user written code, but apparently not in an SQL Join expression.

TurnTheBacon · ‎02-07-2013

I'm working in an SQL Join transformation in DI Studio, and need to make sure that all rows for one specific bank get the flag-variable set to "N". Why can't I just map the BANK_ID and BANK_FLAG from the source table, into BANK_FLAG in the target table, then use the following expression? if BANK_ID = "123456" then do; BANK_FLAG = "N"; end; I also tried this: case BANK_FLAG when BANK_ID = "123456" then "N" else BANK_FLAG end; I'd appreciate any aid on this rookie problem. Thanks for your time.

TurnTheBacon · ‎01-09-2013

I've been working on a large DI Studio job which tidies up historical data. Suddenly, all the connections have become invisible so that the job looks like a large collection of "isolated SQL Join islands". When I enter an SQL Join all the mappings still look correct, but when I try to run the job, the SQL Joins that use subqueries refuse to function. Have any of you encountered a problem such as this before?

TurnTheBacon · ‎01-08-2013

Perfect, now all the other pieces fell into place as well. Thank you.

TurnTheBacon · ‎01-08-2013

Thanks Linus. I know how to join tables to themselves if I can drag-and-drop them from the folders menu into the SQL Join "Create" window, but in this case the input table is a previous SQL Join. How can I make a copy of it in the DI Studio SQL Join Create window? I had hoped to resolve the problem another way. So far I've been grouping by the PK and used expressions to select for example the min(ValidFrom_DTTM) and max(ValidTo_DTTM) to combine 2-3 rows into one. My aim is that the combined row should have the most updated/current disability_flag value.

TurnTheBacon · ‎01-08-2013

Working in an SQL Join transformation in DI Studio, I need an expression which says that "All fields in this column should have the same value as the field where Current=1". Only the rows with Current=1 has "disability_flag" filled out, and I need to copy it to those with Current=0. In example, this input table: PK Disability_Flag Current 1 0 1 1 1 2 0 2 0 2 0 1 Should become this output table: PK Disability_Flag Current 1 1 0 1 1 1 2 0 0 2 0 0 2 0 1 For some reason I can't think of what expression to use. I'd appreciate any advice, thanks.

TurnTheBacon · ‎01-07-2013

Thanks, I've removed the DISTINCT and created an index. Hopefully it'll finish this time. Btw, for some reason DI Studio autoformats this: MASTER.CUST_NO NOT IN (SELECT CUST_NO FROM EXCLUDE) Into this: MASTER.CUST_NO NOT IN (SELECT EXCLUDE.CUST_NO FROM EXCLUDE) It seems a bit odd that the table name EXCLUDE is repeated like that. Does it matter? If I remove the FROM, I get a syntax error.

TurnTheBacon · ‎01-07-2013

Imagine that you have two tables: - One master table that contains all the data you need. It has a PK called CUST_NO. - One control table that determines what data should never be included in queries. It only has one column, also called CUST_NO. I want to use an SQL Join transformation in DI Studio that should extract all the rows from the master table, except for the ones with a CUST_NO that matches the CUST_NO from the control table. How do I perform this query most efficiently? I've tried the two following approaches, but I suspect something is wrong, because they never seem to finish. 1. MASTER.CUST_NO NOT IN (SELECT DISTINCT CUST_NO FROM EXCLUDE) 2. MASTER.CUST_NO ^= EXCLUDE.CUST_NO Thanks for your time.

TurnTheBacon · ‎01-07-2013

I'm working in a DI Studio job that someone else have created, and have a question. One of the variables in an extract is called Birth_DT. It's mapped in from two variables from the previous work table, namely Birth_DT and Master_ID (which is the PK). It has the following expression: coalesce(BIRTH_DT, fnr_birthdate(MASTER_ID)) I'm well aware of how coalesce works, but what does "fnr_birthdate(MASTER_ID)" do? The term "fnr_birthdate" doesn't appear anywhere else in the code. Thanks.

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: Help selecting a particular date from a data set

Help selecting a particular date from a data set

Re: DI Studio 4.7, work table question

DI Studio 4.7, work table question

Re: Flagging rows based on certain criteria

Re: Flagging rows based on certain criteria

Re: Flagging rows based on certain criteria

Re: Flagging rows based on certain criteria

Re: Flagging rows based on certain criteria

Flagging rows based on certain criteria

Re: Advice on how to modify an SQL Join expression (case statement)

Re: Converting yyyymmdd. to DATE9.?

Re: Sending out alerts based on weekdays, need to accommodate for dela...

Re: Automatically filling out control table with numeric values for lo...

Re: How to convert Character variables w/spaces into Numeric ones, in ...

Converting yyyymmdd. to DATE9.?

Re: Converting yyyymmdd. to DATE9.?

Re: Converting yyyymmdd. to DATE9.?

Converting yyyymmdd. to DATE9.?

Re: Reading substrings by means of SCAN or alternative

Reading substrings by means of SCAN or alternative

Problem writing to path over 200 characters, is there a way around thi...

Re: Question about setting up a simple expression in an SQL Join

Question about setting up a simple expression in an SQL Join

DI Studio bug, all connections suddenly become invisible and SQL Join ...

Re: Help with simple SQL Join expression

Re: Help with simple SQL Join expression

Help with simple SQL Join expression

Re: Question about troublesome SQL Join

Question about troublesome SQL Join

Question about part of an expression in a DI Studio job