BookmarkSubscribeRSS Feed
RamKumar
Fluorite | Level 6

Any possibilities to produce a SCD type 1 & SCD type 3 report in base\advanced SAS code? If so, I request you to provide me the outline.

Thanks!

7 REPLIES 7
Patrick
Opal | Level 21

What do you mean by "report"? SCD is mainly a way of how to store history in a table - and the main work is how to load the data into such a table structure. If you've got already a SCD table (either 1 or 3) then it shouldn't be that hard to query data and create a report. Not sure though what a "SCD 3 report" would be.

In DI Studio there is a loader for SCD1 and one for hybrid SCD1/SCD2 loading of a table. Anything else (like SCD3) is not "out-of-the-box" but you can code whatever you like using SAS language.

Designing and implementing code for SCD loading techniques is not that simple so don't expect that someone serves you the solution "on a plate".

Happy though to give you some feedback if you post in detail what you want to achieve and also show us what work and own thinking you've already done.

RamKumar
Fluorite | Level 6

Thanks Patrick for your response.

I meant output table as an report. Yes, I've achieved the SCD's in DI but I wish to implement the same via coding. Because there may a situation in near future where I need to implement the SCD's via base\advanced SAS.

I understand it is difficult to achieve it in coding and hence I request you to provide me the outline which I already mentioned in my earlier post.

Thanks.

Patrick
Opal | Level 21

If you have access to DIS then why don't you look into SCD2 Loader generated code? This will give you a very good starting point for a user written version.

The DIS generated code is something which needs to work for a multitude of environments and any volumes of source and target table. If you develop your own user written version for a specific application then it's very possible that you can come up with more efficient code. I've had to do this once for an implementation (where they had no DIS) and managed to get the load done with a single pass through the data (for SCD2).

If you look into the DIS generated code then you'll see that the main approach is to compare records matching over business key. You create digest values (hex32 representation of md5 values for the variables under change tracking) and you then compare these digest values:

- Matching business key and digest values the same: do nothing

- Matching business key and digest values different: For type1: update the record in target, For  type2: update the record in target with an end datetime of "new start datetime - 1 second", insert new source record

- No matching business key in target: Insert the new record from source.

And then there are things like "expire records in target but not in source" or maintenance of a generated key (surrogate or retained key) which will need some additional coding if you want it to implement.

RamKumar
Fluorite | Level 6

Thanks again for your response. It would be grateful if you can explain the term 'digest values' in brief.

Patrick
Opal | Level 21

SAS(R) Data Integration Studio 4.8: User's Guide

Assuming you're actually having access to DIS then look into SCD2 loader generated code and search for "digest"

CTorres
Quartz | Level 8

You can also use the MD5 function to create a message digest (digital signature), which is nearly unique for each string that is passed to the function.

Using this function you can quickly check if one or more variables (from a list) have changed and generate or not an SCD type 2 or 3 record.

CTorres

LinusH
Tourmaline | Level 20

It seems that you still are confusing the concepts of report and table (data set), which is strange if you have experience with DI Studio.

Patrikcs advice on looking in the existing SCD Type Loader code is the best way to get started.

Do already have a business case for manual coding SCD?

I think the first priority is the convince the application owners that an ETL tool approach is much better than creating and manging hand written code.

Data never sleeps

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
  • 7 replies
  • 2348 views
  • 1 like
  • 4 in conversation