Hi again Chris, That's a really good idea, maybe for a few biggest tables more efficient will be split by columns and not by rows and compare like col by col(or subset of columns by subset of columns using md5 hash, as you proposed).     Thanks so much for the advice! ... View more

Hi again Chris, Thanks for the clarifications! Generally, you are right, as I can see now  - proc compare extremely efficient if use it in the right way - with ID columns and sorted data sets etc. But exactly in my case -  I know for sure that counts of tables match and they are sorted in diff. way and no defined Key etc., Cases I would like to "catch" are like encoding issues during the migration, dates/decimals issue etc. What I already figured-out: For small and AVG tables  - Except/Minus way works ok, it doesn't require defining PK for the table and so on. Interesting point - according to my tests except/minus works almost the same time as proc compare with ID - NoSort option. For huge tables(from 5GB till 100GB in my case) - neither proc compare with ID nor Except doesn't perform. Of course it depends on SAS configuration(CPU etc.), but in an actual situation using actual resources - the best way I see is split huge tables into smaller parts and compare part by part. A lot of tables doesn't have PK as I wrote before, but all have at least 1 index, so my approximate plan is: Calculated on the fly for how many parts the table should be split so each part has around 1GB. Split table using most "distributed" column from the available index, usually it's some Surrogate Key, BIGInt. Compare each of that cut sub-tables in the loop(starting from the newest data), one by one. If there will be some difference - big probability it will be found on the first part, stop comparing after first founded diff. Logging on fly - so ech time it's visible that f.e. 40% of table X is processed already, no differences etc., so some trace file will be loaded before and after each step Point two is the most interesting from tech side. Looks like if the table is indexed and even if it's huge  - the SAS code like select distinct colA from tab1 Executes very quick if there is an index on colA, so having such "keys table" and having table size  - very easy split table on N approximately same size parts, so we'll know that part 1 subtable starts from colA value 10 and ends with colA value 12345 , part 2 starts with cola value 12346 and end with value ...   Generally, it seems like it was much easier move data from SAS to other DB then compare if data matched after the move:)     ... View more

Hi again, I guess I'll go next way - if simple minus/except doesn't match - will do except for all character and numeric columns without decimal part, if that part of tables match - that means issue is in that decimal columns, at least something:) Set of columns will be possible extract using proc content/macro variables manipulations etc.:) Alternative way just instead of select * - do select &column_LIST  and form &column_LIST based on data type - if decimal - round, if no -leave as it is, but offcourse that will impact on perfopmance ... View more

Thanks everyone for advices! Tried to use proc compare like: proc compare base=saslib.tableX compare=DBLib.tableX NOPRINT method=absolute criterion=0.001 outnoequal out=noeqCols; ID _ALL_ NOTSORTED; run; But seems like there is no way to use _ALL_ for the compare proc.   The problem is that for half of the tables there is no PK in the descriptive portion so not possible to use the ID statement, additionally seems like that tables sorted in a different way, so if sort both tables before comparing  - it will be too heavy I guess, some of the tables are tens of Gigabytes. I planned for such huge tables test only first/last million of rows, but the issues with decimals remain the same.   Thanks! ... View more

Thanks! Will use the options ... View more