BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cnilsen
Quartz | Level 8

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.

 

 

 

- Chris N.
1 ACCEPTED SOLUTION

Accepted Solutions
RLigtenberg
SAS Employee

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

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20
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
Data never sleeps
cnilsen
Quartz | Level 8

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.

- Chris N.
RLigtenberg
SAS Employee

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

cnilsen
Quartz | Level 8

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!  Smiley Happy

 

-Chris N.

 

 

- Chris N.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 4 replies
  • 1318 views
  • 0 likes
  • 3 in conversation