08-24-2014 02:47 PM
What do these reports look like? Are you actually asking about reports or about how to accomplish a special type of processing described by these terms?
Obviously, SCD type 1, type2, etc. are some kind of special report and/or process and your lack of a response indicates that forum participants might not know what "SCD type 1" or "SCD type 3" reports look like. Or, if they know what they look like, then they don't have data to test with, since you did not provide any test data or a program that illustrates the code that you've tried.
If you are asking about "slowly changing dimension" processes and possible reports, such as the type 1 example discussed here SCD Type 1,Slowly Changing Dimension Use,Example,Advantage,Disadvantage, then it appears that there is no standard report for type 1 and the "new" record simply replaces the "old/original" record and no history is kept. In that case, I assume a simple PROC PRINT for a "before" and a PROC PRINT for an "after" would be sufficient "reporting". If what you are asking is how to actually perform the update, that is a different question and is probably best answered by looking at descriptions of DATA step programs to do data set updates.
Complicating your question and pertinent to the answer is whether the files involved are SAS datasets, relational data base tables (such as Oracle or DB2) and the permissions involved for updating the files.
If you are NOT talking about Slowly Changing Dimensions, then you will need to elaborate on what you are asking about.
08-25-2014 03:32 AM
Ideally I used to perform SCD's (1,2 & 3) in DI Studio transformation. But I wish to understand how it can be accomplished in via SAS coding. Unfortunately I don't have data at the moment to share you though.
My source and target is SAS datasets. It would be fine if you can explain me with the sample data or just provide me the outline especially for SCD-3.
08-25-2014 10:25 AM
About Type 3. Follow Patricks (and SCD Type 2 Loader transform generated code) guidelines for finding last valid record, and join with input data. You are probably looking at if/case assignment logic for moving old information to the "old columns" and placing up date information oin the current columns.
BTW, I rarely use Type 3 columns, especially not in conformed dimensions, never in a detail data store. Perhaps in some user specific information mart...?