Oracle NVL equivalent in SAS
22,111
Solution 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.
Solution 2
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.
Author by
user3489870
Updated on June 13, 2020Comments
-
user3489870 almost 4 years
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
-
MDiesel almost 10 yearsNVL means that if the value is null, use the value specified by the second parameter
-