BookmarkSubscribeRSS Feed
CG1
Calcite | Level 5 CG1
Calcite | Level 5

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.

5 REPLIES 5
FreelanceReinh
Jade | Level 19

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
Lapis Lazuli | Level 10

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
Steelers_In_DC
Barite | Level 11

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2448 views
  • 3 likes
  • 5 in conversation