SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Issue with SCD Type 2 in SAS Data Integration Studio (SAS DIS)

Frequent Contributor
Posts: 97

Issue with SCD Type 2 in SAS Data Integration Studio (SAS DIS)

Hello everyone.


I am using SAS Data Integration Studio. Here is the thing.

In one of the job, I am using SCD Type 2.


data source;
 input ElectricTag  Electric_RK  PanName PanRK ;
 datalines;2009-AAA 100 USA 50
2010-AAA 101 USA 50 2011-AAA 102 UK 51 2011-BBB 103 POL 52 2011-CCC 104 UKR 53


The business key is ElectricTag and PanName.  When i say Business key, I am referring to the primary key which is the ElectricTag and PanName.


The target table will have 2 additioinal column

1) Valid_from- Capture the datetime now for any new records

2) Valid_to-Capture the datetime now for any expired/obsoleted records


The scenario is, whenever the newer set of data source comes in, for example, a new ElectricTag called 2011-CCD 105 with the same PanName  UKR 53, but the new source doesn't have 2011-CCC anymore.


Hence, i would expect the record below

2011-CCC 104 UKR 53

 have Valid_to with today's datetime to indicate this record is no more valid for use.


Meanwhile, the record below

2011-CCD 105 UKR 53

should have valid_from with today's datetime to indicate this record is valid for use.




Currently, my settings for SCD Type 2 doesnt work properly. nothing will be changed at the moment. I need your kind advice based on my SCD Type 2 settings below:

Business Key tab:



Detech changes tab:



P/S: Electric_name=Electric_tag but with different variable name


I need your advice to fulfill the mentioned criteria.

Respected Advisor
Posts: 4,779

Re: Issue with SCD Type 2 in SAS Data Integration Studio (SAS DIS)

[ Edited ]
Posted in reply to imdickson


SCD2 change tracking will maintain history for values under change tracking which change within the same business key.


If I understand what you describe right then if the value of Electric_Tag changes the value of Electric_Name changes as well.


Electric_Name is part of your business key so you end up with a new business key and not with a change to an existing business key.

For this reason the SCD2 loader will treat this as a totally new record (insert) without expiring (update) an already existing record (which has a different business key).

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation