topic SCD type 2 not working properly in SAS Data Management

SCD type 2 not working properly

MG18 — Mon, 06 Aug 2018 17:23:56 GMT

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 🙂

Re: SCD type 2 not working properly

LinusH — Mon, 06 Aug 2018 21:16:16 GMT

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.

Re: SCD type 2 not working properly

MG18 — Tue, 07 Aug 2018 09:04:10 GMT

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.

Re: SCD type 2 not working properly

MG18 — Tue, 07 Aug 2018 12:37:43 GMT

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

Re: SCD type 2 not working properly

LinusH — Tue, 07 Aug 2018 20:35:25 GMT

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

Re: SCD type 2 not working properly

MG18 — Thu, 16 Aug 2018 10:12:52 GMT

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

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.
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.
Select OK to save changes.

Thanks All for Your reply and suggestion 🙂