CODEWITHSUNDEEP
sqloracle-databasesas
user3489870
user3489870

Reputation:

Oracle NVL equivalent in SAS

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

Upvotes: 4

Views: 28599

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 7

Joe
Joe

Reputation: 63424

NVL in Oracle is equivalent to coalesce in SAS. It says to pick the first nonmissing value from a list; so if you have NVL(A,B,C,0) for example, if A is missing and B is missing and C is missing it will return 0; if one of them is non missing, it will return the earliest one that is nonmissing.

Upvotes: 1

Related Questions