NULL Values

[CG1]

Hi,

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;
quit;

COALESCE_TXT_1_GENDER_CD__3__

________________________
1
3
6
0
8
5
7
9
4

2

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

Thaks in advance.

[FreelanceReinh]

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.

[kannand]

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. ..!!!

[Steelers_In_DC]

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

or

where not missing(COALESCE_TXT_1_GENDER_CD__3__);

[Tom]

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.

[kannand]

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.