topic Re: SCD Type 2 Loader Modification in SAS Data Management

SCD Type 2 Loader Modification

snakes — Tue, 17 Mar 2026 14:46:12 GMT

Hello All,

We would like to make some changes to the SCD Type 2 loader in SAS DI. Is it possible to copy the standard loader to the custom transformation folder so that we can do changes to it?

The reason we want to do it is to enhance the option "Close out records not in source table". Currently it set sets the
ETLS_CLSDATE = DATE();

And we would like to specify the date and not the current date when the program is running.

Regards,

Nagraj

Re: SCD Type 2 Loader Modification

LinusH — Wed, 18 Mar 2026 09:04:36 GMT

That is one flaw many have discovered.

Unfortunately, you cannot tweak the DI Studio native transformations, unless you a re a skilled Java developer.

What you can do is take the generated code from the standard transformation, and paste into a User Written Transformation.

It will not be as "nice" as the original one, and some adjustments/configuration to make it work.

Re: SCD Type 2 Loader Modification

snakes — Wed, 18 Mar 2026 10:50:34 GMT

I am used to programming in java. If you can point me to the .jar or .class file I can try to reverse engineer it. Of course source code will be a lot easier 🙂

Re: SCD Type 2 Loader Modification

LinusH — Wed, 18 Mar 2026 12:50:36 GMT

This is a outside my realm. I found this (old) paper:

https://www.google.com/url?sa=t&source=web&rct=j&opi=89978449&url=https://support.sas.com/resources/papers/proceedings/proceedings/forum2007/015-2007.pdf&ved=2ahUKEwi63Ma-vamTAxVDExAIHUkvPEkQFnoECB8QAQ&usg=AOvVaw02Ha3_unK5TGPkuKH_Td9S

May contact you SAS representative or open a SAS track to get better/more detailed guidelines.

One caveat, if you build you own java transformation, that will for not be eligible for migration to Viya 4 when that day comes (please verify with SAS).

Re: SCD Type 2 Loader Modification

snakes — Thu, 19 Mar 2026 14:59:07 GMT

Thanks, Yes I am aware of this. I was hoping something will have changed after such a long time. :-). I have a basic custom transform that does the job. Back to the drawing board for now.

Re: SCD Type 2 Loader Modification

LinusH — Fri, 20 Mar 2026 07:51:03 GMT

Another (and not ideal) work-around if you wish to use the existing transformation:
- specify %let etls_debug = 1; in your job(s), or perhaps in appserver_autoexec_usermods.
- Have program (macro preferably) in your postcode of your SCD2 transform (or as a separate transformation) that picks up work.etls_close, modify the end date (for records equal today - drop the rest), and re-apply it to you target table. Of course, if you are expecting a big amount of records to be closed out each batch, this is probably not acceptable from a performance standpoint.

Re: SCD Type 2 Loader Modification

snakes — Fri, 20 Mar 2026 14:44:53 GMT

Thanks that is another method that I did not think about. Will look into it.