BookmarkSubscribeRSS Feed
vironica
Calcite | Level 5
Dear All,

This is regarding transformation object : SCD Type 2 Loader for Update/overwritten data (Type 1 Columns) in SAS Data Integration Studio 4.2.

Question:
How to get the date/time(sysdate) when any records got Updated/overwritten by (Type 1 Columns) so that i can keep the time information into my update_dt field in my target table.


Please advise.

Thank you.

Regards,
V
3 REPLIES 3
Patrick
Opal | Level 21
Hi V

Have you tried to add your target field update_dt to "Options/Additional Loader Options/Load time column"?

HTH
Patrick
vironica
Calcite | Level 5
Dear Patrick,

Yes, great ... it works well for the updated records.

If i also need to get the sysdate for new records inserted, how to get this information ?

Please advise.

Thank you very much.

Best Regards,
V Message was edited by: vironica
Patrick
Opal | Level 21
Hi V

Once you've defined your variable as "Load time column" the SCD2 loader will populate it for all cases (update, insert - SCD1, SCD2 and mixed loading).

The value used to populate the "Load time column" is actually not &sysdate but a string based on &sysdate and &systime and stored in a macrovar with name &etls_loadtime.

If you look into the generated code of the SCD2 loader you will find something like this: update_dt = input("&etls_loadtime", datetime20.);

If your SAS coding level is good enough then it's worth taking the time and try to understand the generated code. It helps a lot to understand what's going on behind the scene if you have to debug a problem - and as some code parts are rather advanced there is also a lot to learn.

HTH
Patrick

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1541 views
  • 0 likes
  • 2 in conversation