BookmarkSubscribeRSS Feed
jdebru
Calcite | Level 5

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!

4 REPLIES 4
BrunoMueller
SAS Super FREQ

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;

 

 

 

 

 

Patrick
Opal | Level 21

@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.

jdebru
Calcite | Level 5

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!

LinusH
Tourmaline | Level 20

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.

Data never sleeps

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
  • 4 replies
  • 944 views
  • 0 likes
  • 4 in conversation