I've used option "close out records not in source table" already many times without any issues. I would have bet that this always works - and lost!
I can confirm and replicate what you observe. You can resolve the issue by adding a current indicator column.
Are you going to contact SAS TechSupport? This looks to me like a bug which requires fixing so if you're not telling SAS "officially" then I will.
Details
I've done some testing and what I found: If you also define a current indicator column under change tracking (which I always do out of habit) then the DIS transformation generates code which behaves as you would expect.
If there is a current indicator column defined then the SCD2 generated code only selects "active" records from the target table.
/* Sort target table records before creating the temporary cross reference */
/* table: */
proc sort data = t4BASE.Target(where = (change_current_ind = 'Y'))
out = work.etls_sortedxref(keep=FINANCIAL_INSTRUMENT_ID VALID_FROM_DTTM VALID_TO_DTTM change_current_ind
OTHER_VAR);
by FINANCIAL_INSTRUMENT_ID;
run;
And then in the step where records for expiration get determined there is nothing coming from source nor target so no output record for closing gets created (which is what you want as such records have been closed already in earlier runs).
/* Close out any records that are in the xref table but not in the source. */
else if inXref and not inSort then
do;
ETLS_CLSDATE = DATETIME();
output work.etls_close;
end;
Now if you don't have a change current indicator column defined then the SCD2 generated code looks like:
/* Sort target table records before creating the temporary cross reference */
/* table: */
proc sort data = t13BASE.Target
out = work.etls_sortedxref(keep=FINANCIAL_INSTRUMENT_ID VALID_FROM_DTTM VALID_TO_DTTM OTHER_VAR);
by FINANCIAL_INSTRUMENT_ID VALID_TO_DTTM;
run;
So here ALL records get selected and though if not existing in source these records get expired over and over again. This is not only inefficient but it's also wrong as the expiration end date gets changed to a date later than when the record actually expired.
I believe that for the SCD2 loader to behave correctly even without a change current indicator, the generated code would need to include a selection on current records only - where = (VALID_TO_DTTM = &etls_enddate)
/* Sort target table records before creating the temporary cross reference */
/* table: */
proc sort data = t4BASE.Target(where = (VALID_TO_DTTM = &etls_enddate))
out = work.etls_sortedxref(keep=FINANCIAL_INSTRUMENT_ID VALID_FROM_DTTM VALID_TO_DTTM change_current_ind
OTHER_VAR);
by FINANCIAL_INSTRUMENT_ID;
run;
And just for completion - and something to share with SAS TechSupport - here also attaching the DIS 4.903 job I've created for testing.
Objects in .spk
SASWORK physical path (points to WORK so should work in any environment)
To replicate my testing: After first execution out of DIS don't close job but add below definition, then execute again
Result after two executions
The test job is set-up so that there is a gap of 1 second per iteration (using call sleep).
There are 4 iterations per job execution (4 SCD2 loads of data with the same business key).
1. Brand new record
2. Change record (same value for business key but changed value for type2 tracking variable "other_var")
3. No records in source table
4. No records in source table
Test result
When there is no change current indicator defined then the value for VALID_TO_DTTM gets changed every single time for records in target but not in source. This is considered to be an undesired behavior.
When there is a change current indicator column defined then records get only expired once and later runs don't change the valid_to_dttm value anymore. This is considered to be the correct behavior.
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.
-
3402 views
-
5 likes
-
-
