SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SCD type 2 not working properly

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

SCD type 2 not working properly

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 Smiley Happy   


Accepted Solutions
Solution
Thursday
Frequent Contributor
Posts: 90

Re: SCD type 2 not working properly

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 Smiley Happy 

View solution in original post


All Replies
Super User
Posts: 5,919

Re: SCD type 2 not working properly

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.
Data never sleeps
Frequent Contributor
Posts: 90

Re: SCD type 2 not working properly

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.

Frequent Contributor
Posts: 90

Re: SCD type 2 not working properly

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

Super User
Posts: 5,919

Re: SCD type 2 not working properly

Solution
Thursday
Frequent Contributor
Posts: 90

Re: SCD type 2 not working properly

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 Smiley Happy 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 129 views
  • 0 likes
  • 2 in conversation