topic Re: Can proc compare treat zero and null as the same? in SAS Enterprise Guide

Can proc compare treat zero and null as the same?

Martin_Bryant — Mon, 21 Dec 2020 09:04:43 GMT

I know that there is the NOMISSING option to ignore missing values entirely, but I just want to be able to treat nulls in numeric fields as equal to a zero, but not equal to any non-zero value. Is this possible?

Re: Can proc compare treat zero and null as the same?

andreas_lds — Mon, 21 Dec 2020 10:29:12 GMT

@Martin_Bryant wrote:

I know that there is the NOMISSING option to ignore missing values entirely, but I just want to be able to treat nulls in numeric fields as equal to a zero, but not equal to any non-zero value. Is this possible?

I don't see an option to tread missing values as zeros. Attaching a format to the variable does not work as expected, same for using the missing-option.

Re: Can proc compare treat zero and null as the same?

Martin_Bryant — Mon, 21 Dec 2020 12:04:30 GMT

I would have hoped there there was an option to allow all empty values to be treated as equal. Like isempty() in most languages. Zero == null and (empty string) "" == null.

Re: Can proc compare treat zero and null as the same?

Tom — Mon, 21 Dec 2020 17:52:56 GMT

Nope. You could make versions of your data that replace missing with zero and then compare those versions.

proc stdize data=A out=A_zero missing=0 reponly;
  var _numeric_;
run;
proc stdize data=B out=B_zero missing=0 reponly;
  var _numeric_;
run;
proc compare data=A_zero compare=B_zero ;
run;

Re: Can proc compare treat zero and null as the same?

Martin_Bryant — Tue, 22 Dec 2020 10:39:05 GMT

Thanks, I'll have to learn exactly how that works, but sounds a possibility.

Re: Can proc compare treat zero and null as the same?

Martin_Bryant — Tue, 29 Dec 2020 08:45:24 GMT

Whilst this is a good work around, I need something that I can use on multiple projects and I was hoping for minimal effort. (I'm a lazy sod.) Anyway, thanks. 🙂