topic Re: NULL Values in SAS Programming

NULL Values

CG1 — Tue, 17 Nov 2015 07:59:26 GMT

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

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

Thaks in advance.

Re: NULL Values

FreelanceReinh — Tue, 17 Nov 2015 09:12:33 GMT

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.

Re: NULL Values

kannand — Tue, 17 Nov 2015 09:45:29 GMT

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

Re: NULL Values

Steelers_In_DC — Tue, 17 Nov 2015 13:48:57 GMT

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__);

Re: NULL Values

Tom — Tue, 17 Nov 2015 13:57:15 GMT

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.

Re: NULL Values

kannand — Tue, 17 Nov 2015 14:23:52 GMT

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.