DATA Step, Macro, Functions and more

NULL Values

Occasional Contributor CG1
Occasional Contributor
Posts: 13

NULL Values



I am new with SAS, I have very small question How to handle NULL values in SAS. In oracle we are using nvl for null so what is the function in SAS to handle null.


libname mydblib oracle user='GAG' password='abcd123' path='prod2' schema='SALES';

proc sql;
select distinct coalesce(gender_cd,'3') from mydblib.cust_addr;







I have null values in output. please suggest how to handle this.


Thaks in advance.

Trusted Advisor
Posts: 1,115

Re: NULL Values

SAS has two similar functions for this purpose: COALESCE for numeric arguments and COALESCEC for character arguments.


Your function call "coalesce(gender_cd_'3')" is inconsistent as it uses a character argument, '3', with COALESCE. Is gender_cd a numeric or a character variable?


That said, I was not able to replicate your result if gender_cd has just a missing value: If I define it as a numeric variable, the PROC SQL step stops with "ERROR: The COALESCE function requires its arguments to be of the same data type." If I define it as a character variable, it is accepted and works fine in spite of using the "wrong" function. So, it would be interesting to see what gender_cd really contains in the last but one observation.

Regular Contributor
Posts: 161

Re: NULL Values

[ Edited ]

The other option,  I'd like to suggest is that if it is a non numeric and non blank value,  check if the var contains anything other than a numeric value(0-9), initialize based on your needs.  I was thinking more of an invisible,  hexadecimal  value that is not visible. ..


Good luck. ..!!!

Kannan Deivasigamani
Valued Guide
Posts: 858

Re: NULL Values

You can use a missing function, this will work with character or numeric.  where missing(COALESCE_TXT_1_GENDER_CD__3__);




where not missing(COALESCE_TXT_1_GENDER_CD__3__);

Super User
Super User
Posts: 6,498

Re: NULL Values

The value in your character variable is not a space, that is why the coalesce did not change it to a '3'.

Use $HEX. format to display the hex code for the value that looks like a blank.  It is probably something like 'A0'X or '09'x or '0D'x.

Regular Contributor
Posts: 161

Re: NULL Values

If you view the data in Notepad++ with the hexadecimal editor plug-in turned on, you'll be able to view the hex character you have and you can code for it or as I mentioned in my previous post above,  you can also check for any non numerics and code for it.  So you've got atleast 2 options.

Kannan Deivasigamani
Ask a Question
Discussion stats
  • 5 replies
  • 5 in conversation