Pearson correlation with nulls

Question

Hello,

I'm trying to run a pearson correlation, and everything goes well except when my data contains nulls.

My data looks like this (actual data attached):

var1var2var353 6257269  513652

When entering this into pearson, the outcome says

FieldNamevar1var2var3var11  var2 1 var3  1

the pearson documentation recommends to use the imputation tool and replace the null values, but it's important that the calculation is identical to the excel calculation.

in Excel, a record is ignored when only one of the two values are null (cf screenshot below)

Is this a setting that I can set to the pearson tool? Or can someone suggest a method to achieve this?

I imagine a batch macro might do the trick, but that sounds kinda sloppy for what I hope is a button that I'm missing or something :)  ?

Thanks,

Mikis

correlation_issue.yxmd

Mikis · Accepted Answer

Hi @MichalM

Thanks, I have built what I think you mean and added it in attachment.

The problem is that it's really slow. The previous pearson correlation with 33 variables took less than a second, this thing takes almost 2 minutes (since the batch macro with 33 variables needs to run 33² = 1089 times).

This correlation is just the first step of a whole flow. If this already takes 2 minutes then I'll probably have to ask my users to just correlate in excel and start the flow from there. But if someone can use it, the package attached might help.

Regards,

Mikis