topic Re: How to handle divide/0 in Proc SQL , case statement ? IfError in SAS? in SAS Programming

How to handle divide/0 in Proc SQL , case statement ? IfError in SAS?

GN0001 — Thu, 01 Jul 2021 22:19:14 GMT

Hello team,

Proc SQL;
select b.this, a.this, 
case
When b.rate <0.25 Then (somenum/b.rate)

b.rate is equal 0, then we have divide by zero. This produces errors in Excel and is handled by IfError.

What is the equal of IFError is in SAS?

respectfully,

Blue blue

PaigeMiller — Thu, 01 Jul 2021 22:25:06 GMT

case when b.rate>0 and b.rate <0.25 then (somenum/b.rate)

This will not produce an error in SAS, but the resulting variable is still missing in SAS.

You can also do this:

case when b.rate <0.25 then divide(somenum,b.rate)

which also gives a missing, but no error.

KatScott — Thu, 01 Jul 2021 22:27:23 GMT

The following will replicate the same behavior as IFERROR.

Proc SQL;
select b.this, a.this, 
case
When b.rate <0.25 Then coalesce(somenum/b.rate.0)

For more information please see:

Function documentation:

Best,

KatScott — Thu, 01 Jul 2021 22:28:28 GMT

One correction I needed a comma:

Proc SQL;
select b.this, a.this,
case
When b.rate <0.25 Then coalesce(somenum/b.rate,0)