topic Re: DI studio question: defaulting a Sysdate into a table column in SAS Data Management

DI studio question: defaulting a Sysdate into a table column

cnilsen — Fri, 03 Mar 2017 19:50:02 GMT

Ok folks, here is my problem of the day:

I am using DI Studio (v4.901)to pull in files, perform some minor mapping, and format changes, then output to a permanant table.

As I am populating the result table, I have 4 extra columns that my table loader procedure has no possible mapping for since they are not in the source table.

3 of them I am defaulting to 0 or blanks using a case statement to avoid null values.(they are for usage later)

The 4th (is actually the last column in the result table.. is a 'Last Update Date' column that I need to default the SYSDATE into as the records are written.

I haven't found an expression/process that I can use to populate this column.

I'm from an AS400 backround where a simple command can accomplish this.. SAS doesnt seem quite as easy.

I have attached a screen shot to give you an idea what I'm looking at.

Any help would be appreciated!

-Chris N.

Re: DI studio question: defaulting a Sysdate into a table column

LinusH — Sat, 04 Mar 2017 07:00:21 GMT

I think that there is a Load time column option in almost all Table Loader transformations.

As for the zeros, no need for case, just enter a 0 in the mapping expression, it will resolve to
0 as column_name

Re: DI studio question: defaulting a Sysdate into a table column

cnilsen — Mon, 06 Mar 2017 14:15:00 GMT

I tried removing the case statements and simply putting a zero into the Expression column..

this worked for the 3 non-date columns.

For the LAST_UPDATE_DT column I could not find a load time option to default in the SYSDATE.

Adding simply 'SYSDATE' into the expression is not valid. Leaving the column unmapped/without expression also gives me a run time error. You cannot do a null value insert into the column. I also tried to use a case staement to put in SYSDATE or TIMESTAMP. the code validator was not very happy with either idea. It says that they are not columns found in my source table

this is the error I receive:

Cannot insert the value NULL into column 'LAST_UPDATE_DT', table 'MDMCDISTGDB.LANDINGZONE.CUSTOMER'; column does not allow nulls. INSERT fails. : [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The statement has been terminated.

any other Ideas?

-Chris N.

Re: DI studio question: defaulting a Sysdate into a table column

RLigtenberg — Mon, 06 Mar 2017 15:24:59 GMT

Hi Chris,

If you are using the “Table Loader” transformation, it has a “Load Time Column” option on the Options tab. The option is in the Additional Loader Options group.

If you want to use an expression try INPUT("&SYSDATE9",DATE9.) in the Expression field on the Mappings tab of the Table Loader properties.

Regards,

Robert

Re: DI studio question: defaulting a Sysdate into a table column

cnilsen — Mon, 06 Mar 2017 15:40:41 GMT

Hi Robert,

Success!!

Thanks for the help. I found the additional loader column and the date populated perfectly. The fast track courses I have taken probably touched on this, but it was a lot to digest in such a short time frame..

I'm going to test the other method as well to see what happens. Its always good to learn more than one way to do something.

Thank you again for the help!

-Chris N.