CODEWITHSUNDEEP
sqlsas
Martin Reindl
Martin Reindl

Reputation: 1603

IIF in Proc SQL

I want to translate the following snippet of code for use in Proc SQL for SAS: 

UPDATE loan_tape
SET apples = IIF(apples != 0.0, apples, 20.0)

However IIF() is not recognized by PROC SQL. Can I implement an if/else or some kind of CASE statement? None of that seems to be working for me.

Upvotes: 2

Views: 2974

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Use case:

UPDATE loan_tape
    SET apples = (CASE WHEN apples <> 0.0 THEN apples ELSE 20.0 END)

Actually, use WHERE:

UPDATE loan_tape
    SET apples = 20.0
    WHERE apples = 0.0 OR apples IS NULL;

This is the best way to do this in any database, because it limits the number of rows being updated (some databases only do the update when the value changes, but it is still better to express this logic as a filter).

I should also note that doing equality comparisons with floating point numbers in SQL (or any language) is dangerous. SQL has fixed point values (decimal/numeric). The problem is that values very, very, very close to zero tend to "look" like 0 -- something like 0.000000000097.

Upvotes: 7

Chris Long
Chris Long

Reputation: 1319

You could also use the SAS IFN() function, which does exactly what IIF() is doing in your example, giving the minimal change from your starting point. As Gordon said, though, using a WHERE clause is probably better for this task.

Documentation page for IFN:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002604573.htm

Upvotes: 2

JohnHC
JohnHC

Reputation: 11205

Use case

UPDATE loan_tape
SET apples = case when apples <> 0.0 then apples else 20.0 end

Upvotes: -2

Related Questions