topic Re: SCD2 Sort Failing w/ non A-Z 0-9 in SAS Data Management

SCD2 Sort Failing w/ non A-Z 0-9

jdebru — Fri, 13 Apr 2018 20:49:41 GMT

We are attempting to maintain history with an SCD2 node loading to a SQL Server table. The Business Key occasionally contains characters other than A-Z and 0-9 at the beginning. When I load the table from empty, it loads fine. When I attempt to run a delta I get "ERROR: BY variables are not properly sorted on data set ". It is referring too the data that was sourced from the Target table for reference. If I remove the non A-Z & 0-9 it all works fine.

The data is stored in the SQL Server Table ordered by the Business Key and I sort the data by Business Key just before the SCD2. Could one sort method put the odd characters at the bottom and the other at the top? Maybe I need to apply a different SORTSEQ somehow? Any insight would be greatly appreciated!

Re: SCD2 Sort Failing w/ non A-Z 0-9

BrunoMueller — Mon, 16 Apr 2018 20:21:24 GMT

I guess it is because the sorting is done in the DBMS and collating sequence of the DBMS sort is different from the way SAS sorts.

As a workaround you can tell the DBMS to use a different sort sequence.

Here is a code sample from Oracle, maybe there is a similar way to do it for your DBMS

libname mydbms
 oracle
 path=xe
 user="uid" password="pw"
 dbconinit="alter session set nls_sort = binary "
;

The information specified for the dbconinit option can also be set under the advanced properties of the library metadata object.

You can test whether it works, by running

data _null_;
  set mydbms.dbtable;
  by col_with_special_chars;
run;

Re: SCD2 Sort Failing w/ non A-Z 0-9

Patrick — Mon, 16 Apr 2018 22:20:31 GMT

@jdebru

Why do you have to pre-sort in first place? Looking into SCD2 transformation generated code I can see explicit sorts in the generated code. But then my sample code will not reflect exactly what you're having.

It would be interesting to understand which step in your code exactly is throwing the error and why no generated pre-sorting is happening there (i.e. do you pass in your source table with a sorteby attribute set?).

....and last but not least: I'm not a big fan of the SCD2 transformation when it comes to using it with a target table in a database (too much data movement to SAS with huge impact on performance). You can also load SCD2 with a rather simple combination of a SQL Update and SQL Insert. If your source table resides in SAS then first upload this table into a staging table and then execute everything in-database. There is no need to spend time creating a hash key using md5() but make sure you've got an index on the target table over the business key.

Re: SCD2 Sort Failing w/ non A-Z 0-9

jdebru — Tue, 17 Apr 2018 12:50:25 GMT

Thank you for the responses Bruno & Patrick! I am not imposing a sort on the target table. There does not appear to be an option to do so. We are working with SQL Server, so I will check the syntax on performing such a change to the sorting of the target in the DB. Initially we did not impose any sorting at all until we received the error. We are using the out of the box functionality in DI Studio. I am trying to stay away from any "custom" SAS code. Building out the SCD2 functionality manually is my last resort. The SCD2 node should be sufficient for our use case. I will check the sorting options and report back with my findings! Thank you!

Re: SCD2 Sort Failing w/ non A-Z 0-9

LinusH — Tue, 17 Apr 2018 13:38:38 GMT

As I recall, we had similar issues with SQL Server, not sure if it was in the SCD Type 2 loader specifically.

But please attach your log so it's easier for us to pinpoint the problem.

You could of course force the libname engine not to pust sorts t the target, but that could impact performance.