BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MG18
Lapis Lazuli | Level 10

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 🙂   

1 ACCEPTED SOLUTION

Accepted Solutions
MG18
Lapis Lazuli | Level 10

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 🙂 

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20
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
MG18
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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

MG18
Lapis Lazuli | Level 10

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 🙂 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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