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.
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
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.
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
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.