# Compare 2 data sets - similar to SAS proc compare

**URL:** <https://discourse.julialang.org/t/compare-2-data-sets-similar-to-sas-proc-compare/79215>\
**Category:** Data\
**Tags:** testing, inmemorydatasets\
**Created:** [April 8, 2022, 9:07am UTC](https://discourse.julialang.org/t/compare-2-data-sets-similar-to-sas-proc-compare/79215 "2022-04-08T09:07:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ab2z](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ab2z/32/34893_2.png) [@ab2z](https://discourse.julialang.org/u/ab2z)\
**Post date:** [April 8, 2022, 9:07am UTC](https://discourse.julialang.org/t/compare-2-data-sets-similar-to-sas-proc-compare/79215/1 "2022-04-08T09:07:50Z")

</div>

Question: what is the best command for comparing 2 datasets - something like proc compare in SAS ?  
I could not edit this [Comparison 2 data sets](https://discourse.julialang.org/t/comparison-2-data-sets/78790) , So I decide to open this new topic.

I have the following example

```julia
old=Dataset(Insurance_Id=[1,2,3,5],Business_Id=[10,20,30,50],Amount=[100,200,300,missing],Account_Id=["x1","x10","x5","x5"])

new=Dataset(Ins_Id=[1,3,2,4,3,2],B_Id=[10,40,30,40,30,20],AMT=[100,200,missing,-500,350,700],Ac_Id=["x1","x1","x10","x10","x7","x5"])

```

The combination of insurance\_id\*Business\_Id gives a unique id.

The delta is on the amount (absolute difference greater than 50) and the Account\_Id.

The only matches here are (insurance\_id,Business\_Id) in {(1,10)} so I expect to have all other combinations.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [April 9, 2022, 2:20pm UTC](https://discourse.julialang.org/t/compare-2-data-sets-similar-to-sas-proc-compare/79215/2 "2022-04-09T14:20:38Z")

</div>

is this the result you want?

```julia
using DataFrames
old=DataFrame(Insurance_Id=[1,2,3,5],Business_Id=[10,20,30,50],Amount=[100,200,300,missing],Account_Id=["x1","x10","x5","x5"])

new=DataFrame(Ins_Id=[1,3,2,4,3,2],B_Id=[10,40,30,40,30,20],AMT=[100,200,missing,-500,350,700],Ac_Id=["x1","x1","x10","x10","x7","x5"])

aon=antijoin(old, new, on= [:Insurance_Id => :Ins_Id,:Business_Id=>:B_Id])
ano=antijoin(new, old, on= [:Ins_Id =>:Insurance_Id,:B_Id => :Business_Id])
rename!(aon,:Insurance_Id => :Ins_Id ,:Business_Id=>:B_Id)

ij=innerjoin(new, old, on= [:Ins_Id =>:Insurance_Id,:B_Id => :Business_Id])

fij=filter(r->r.AMT-r.Amount>50,ij)

vcat(fij,aon,ano, cols= :union)
5×6 DataFrame
 Row │ Ins_Id B_Id Amount Account_Id AMT Ac_Id   
     │ Int64 Int64 Int64? String? Int64? String?
─────┼──────────────────────────────────────────────────────
   1 │ 2 20 200 x10 700 x5
   2 │ 5 50 missing x5 missing missing
   3 │ 3 40 missing missing 200 x1
   4 │ 2 30 missing missing missing x10
   5 │ 4 40 missing missing -500 x10

```

```julia

```

a sketch for a generic function that does this set of operations

```julia

function compare(old, new, oldnames, newnames)
    rename!(old,Pair.(oldnames, newnames))
    aon=antijoin(old, new, on= newnames)
    ano=antijoin(new, old, on= newnames)
    ij=innerjoin(new, old, on= newnames)
    vcat(ij, aon, ano, cols = :union)
end

```

---

<div class="post-metadata">

**Author:** ![DataFrames](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@DataFrames](https://discourse.julialang.org/u/DataFrames)\
**Post date:** [April 10, 2022, 2:10am UTC](https://discourse.julialang.org/t/compare-2-data-sets-similar-to-sas-proc-compare/79215/3 "2022-04-10T02:10:02Z")

</div>

There isn’t a single function for comparison in `InMemoryDatasets`. You need a combination of join functions, filter, and custom code for solving your problem. Check also [this](https://discourse.julialang.org/t/is-there-a-package-to-do-dataframes-comparisons/65713).

---

<div class="post-metadata">

**Author:** ![ab2z](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ab2z/32/34893_2.png) [@ab2z](https://discourse.julialang.org/u/ab2z)\
**Post date:** [April 11, 2022, 7:17am UTC](https://discourse.julialang.org/t/compare-2-data-sets-similar-to-sas-proc-compare/79215/4 "2022-04-11T07:17:31Z")

</div>

thank you for your answers. I opened a request to InMemoryDatasets look  
[https://github.com/sl-solution/InMemoryDatasets.jl/issues/53](https://github.com/sl-solution/InMemoryDatasets.jl/issues/53)  
.  
The comparison can now be done with the compare function in this package, too.

```julia
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 

```

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [April 11, 2022, 6:02pm UTC](https://discourse.julialang.org/t/compare-2-data-sets-similar-to-sas-proc-compare/79215/5 "2022-04-11T18:02:38Z")

</div>

The speed of development of the new features is truly remarkable. 👏  
The function seems quite complex. I can’t understand the role of the third method `eq_fun (x, y) = missing`.  
Even without it or defining it in a different way (for instance, `eq_fun (x, y) = false`), I get the same result.