SCD type 2 not working properly

[MG18]

HI all, 

I have created one DI studio job which has SCD type 2 transformation in it and which is failing with below mentioned error :-

 SCD Type 2
 Line 1,822: ERROR: BY variables are not properly sorted on data set LIBKC.FSK_LIST_ELEMENT. etls_md5= DIGEST_VALUE= LIST_ELEMENT_ID=2683832 LIST_ID=100 LIST_ELEMENT_DESC= LIST_ELEMENT_VALUE= CREATE_DATE=06FEB2018:16:33:50 DELETE_DATE=06FEB2018:16:33:49 FIRST.LIST_ID=1 LAST.LIST_ID=0 FIRST.LIST_ELEMENT_VALUE=1 LAST.LIST_ELEMENT_VALUE=0 FIRST.LIST_ELEMENT_DESC=1 LAST.LIST_ELEMENT_DESC=0 FIRST.DELETE_DATE=1 LAST.DELETE_DATE=0 etls_str= SEGMENT_ID=. CREATE_USER_ID=. DELETE_USER_ID=. LOGICAL_DELETE_IND=. _ERROR_=1 _N_=2
 Line 1,822: WARNING: The data set WORK.ETLS_SORTEDXREF may be incomplete. When this step was stopped there were 0 observations and 7 variables.
 Line 1,822: WARNING: Data set WORK.ETLS_SOURCE was not replaced because this step was stopped.
See: Code | Log | Properties

While trying to resolve this is issue i came to below mentioned SAS Support :-

http://support.sas.com/kb/45/387.html 

Which is clearly suggesting to apply some  hotfix  but other SCD type 2 are working fine so i am confused whether i have to apply this hotfix or not ? and has anyone faced same kind of this issue in SAS DI Studio ,Please help on this .

Many thanks in advances 🙂   

[LinusH]

That hot fix is for Merchandise Intelligence. This hot fix seem more relevant:
http://support.sas.com/kb/45/939.html
What version are you using?
If you have 4.4 or later, and all mappings and options seem correct, try SAS Tech support.

[MG18]

Hi ,

We are using the DI studio 4.6 version and SAS 9.3 version and other SCD type2 are working properly please tell still i need to apply this hotfix.

[MG18]

Kindly let me know there any way to check which all hot fixes has been applied to SAS system how to check this ?

[LinusH]

http://support.sas.com/documentation/cdl/en/whatsdiff/63859/HTML/default/viewer.htm#p1liyuy27ouzuvn1...

[MG18 · Accepted solution]

We have made the changes as per below and after this DI job is  running fine :-

1. If you don't see a PROC SORT on the Target Table in the code, then you need to open Properties on the SCD Type 2 Loader transformation, go to the Options tab, and on the SCD group set the option "Sort target table records before creating the temporary cross reference table" to YES.
2. In the Additional Options group on the same Options tab for the SCD Type 2 Loader transform, enter SORTPGM=SAS in the System options: box.
3. Select OK to save changes.

Thanks All for Your reply and suggestion 🙂