compare 2 data sets like SAS proc compare

[ab2z]

can you create something like proc compare in SAS, so that the user can compare two dat sets easily?
see also https://discourse.julialang.org/t/compare-2-data-sets-similar-to-sas-proc-compare/79215?u=ab2z

thanks+regards

[sl-solution]

Let me think about it, I think it is a good addition. I may add features to compare, however, I guess that would be breaking.

[sl-solution]

I will update the compare function to support more complicated comparisons. Only the on keyword argument will change its purpose; instead of defining the columns which are needed to be compared, on will indicate the key columns. A new keyword, cols, will be used for specifying the columns pairs. This is breaking but makes more sense.
The new function will also be able to handle two data sets with different number of rows.

[sl-solution]

InMemoryDatasets now (version 0.7.0+) is equipped with a more flexible compare function. The new function supports comparing two data sets with different number of rows. It also supports comparing two data sets using matching observations.

The compare function in IMD

• doesn't require sorted inputs,
• allows any user defined function to be used for comparing values,
• includes all comparisons in the output data set,
• uses left and right terminology as base and compare, respectively,
• when multiple matching observations exist, it compares all combinations. User may pass multiple_match = true to distinguish those observations,
• allows comparing values based on formatted values

compared to PROC COMPARE in SAS.

Regarding your question, the following code provides a solution;

julia> eq_fun(x::Number, y::Number) = abs(x - y) <= 50

julia> eq_fun(x::AbstractString, y::AbstractString) = isequal(x,y)

julia> eq_fun(x,y) = missing

julia> res = compare(old, new, 
                     on = [:Insurance_Id => :Ins_Id, :Business_Id => :B_Id],
                     cols = [:Amount => :AMT, :Account_Id => :Ac_Id],
                     eq = eq_fun)
7×6 Dataset
 Row │ Insurance_Id  Business_Id  obs_id_left  obs_id_right  Amount=>AMT  Account_Id=>Ac_Id 
     │ identity      identity     identity     identity      identity     identity          
     │ Int64?        Int64?       Int32?       Int32?        Bool?        Bool?             
─────┼──────────────────────────────────────────────────────────────────────────────────────
   1 │            1           10            1             1         true               true
   2 │            2           20            2             6        false              false
   3 │            3           30            3             5         true              false
   4 │            5           50            4       missing      missing            missing 
   5 │            2           30      missing             3      missing            missing 
   6 │            3           40      missing             2      missing            missing 
   7 │            4           40      missing             4      missing            missing 

You may call filter to filter rows with differences, e.g.

julia> filter(res, r"=>", type = any, by = !isequal(true))