Hello Team ,
Is there any option with DIS SCD type 2 transformation , to perform this action only when it meets specific condition
ex: update the specific records as in below example
we need to implement SCD type 2 transformation only for the dept =EEE for other department records , we need to keep as is .
am using below configuration
Here the business key is NAME and DEPT (we have unique records)
detect changes in column Score
student_key | Name | dept | Score | change_ind | change_start_dt | change_end_dt |
1 | Student1 | EEE | 70 | Y | 09/03/2022 | 01/01/5000 |
2 | Student2 | EEE | 50 | Y | 09/03/2022 | 01/01/5000 |
3 | Student1 | CSC | 70 | Y | 09/03/2022 | 01/01/5000 |
Hello,
I am not aware of a specific option within the SCD Type 2 Loader transformation that would satisfy the requirement if I am understanding the problem correctly. However, a potential workaround that you could test for suitability would be to apply a filter to the source (incremental change data) that would result in outputting only those rows where dept=EEE. The pre-filtered source data would then be connected as an input (source) to the SCD Type 2 Loader. The SCD type 1/2 processing should then only produce insert/update rows that are specific to those rows where dept=EEE.
Please not, I have not tested this workaround. I would recommend thoroughly testing this workaround if you would be pursuing it.
Cheers.
Hello,
I am not aware of a specific option within the SCD Type 2 Loader transformation that would satisfy the requirement if I am understanding the problem correctly. However, a potential workaround that you could test for suitability would be to apply a filter to the source (incremental change data) that would result in outputting only those rows where dept=EEE. The pre-filtered source data would then be connected as an input (source) to the SCD Type 2 Loader. The SCD type 1/2 processing should then only produce insert/update rows that are specific to those rows where dept=EEE.
Please not, I have not tested this workaround. I would recommend thoroughly testing this workaround if you would be pursuing it.
Cheers.
From an architectural point of view, I cringe when you have different update methods for different rows in the same table.
I sugest that you keep the table a pure SCD Type 2, and then if you have different requirement from a user perspective, create a view on top (or make an extract to another table) to hide history records for non EEE records.
That said, there is an option "Close out records not in the source table" that you can set to "No". And then add another Table Loader to load for non EEE records (Table laoder). This is just an idea, haven't tested myself.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.