BookmarkSubscribeRSS Feed
imdickson
Quartz | Level 8

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:
Electric_Name

Pan_CD

 

Detech changes tab:

Electric_Tag

 

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

 

I need your advice to fulfill the mentioned criteria.

1 REPLY 1
Patrick
Opal | Level 21

@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).

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
  • 1 reply
  • 676 views
  • 2 likes
  • 2 in conversation