Oracle NVL equivalent in SAS

Question

I need to convert the sql code to sas code;

  NVL(SPRTELE_STATUS_IND, 'A') = 'A' 
  AND NVL(SPRTELE_PRIMARY_IND, 'Y') = 'Y' 
  AND NVL(SPRTELE_SEQNO, 99) = 

    (SELECT NVL(MAX(SPRTELE_SEQNO), 99) 
   FROM SATURN.SPRTELE D 
  WHERE SPRIDEN_PIDM = D.SPRTELE_PIDM 
    AND D.SPRTELE_TELE_CODE = 'MA' 
    AND NVL(D.SPRTELE_STATUS_IND, 'A') = 'A' 
    AND NVL(D.SPRTELE_PRIMARY_IND, 'Y') = 'Y'))

How can I convert NVL to sas? what is that mean

Answer 1

The correct function to use instead of NVL() is COALESCE(). This is the ANSI standard function and supported by SAS, Oracle, and most other databases:

  COALESCE(SPRTELE_STATUS_IND, 'A') = 'A' 
  AND COALESCE(SPRTELE_PRIMARY_IND, 'Y') = 'Y' 
  AND COALESCE(SPRTELE_SEQNO, 99) = 

    (SELECT COALESCE(MAX(SPRTELE_SEQNO), 99) 
   FROM SATURN.SPRTELE D 
  WHERE SPRIDEN_PIDM = D.SPRTELE_PIDM 
    AND D.SPRTELE_TELE_CODE = 'MA' 
    AND COALESCE(D.SPRTELE_STATUS_IND, 'A') = 'A' 
    AND COALESCE(D.SPRTELE_PRIMARY_IND, 'Y') = 'Y'))

There may be other differences in the full query.