11-17-2015 02:59 AM
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';
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.
11-17-2015 04:12 AM
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.
11-17-2015 04:44 AM - edited 11-17-2015 04:45 AM
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. ..!!!
11-17-2015 08:48 AM
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__);
11-17-2015 08:57 AM
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.
11-17-2015 09:23 AM
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.