topic Re: Case when to handle blank and NULL values in SAS Programming

Case when to handle blank and NULL values

Babloo — Sun, 04 Feb 2024 17:28:02 GMT

Below case when is correct to handle, "If the values are not available or only ZIP is available then I do not want to populate hyphen "-".?

case when ZIP IS NULL or ' ' then ZIP||ZIP_PLUS
when ZIP_PLUS IS NULL or ' ' then ZIP||ZIP_PLUS
else ZIP||'-'||ZIP_PLUS
end as PRO_ZIP

Re: Case when to handle blank and NULL values

PaigeMiller — Sun, 04 Feb 2024 17:48:30 GMT

@Babloo wrote:

Below case when is correct to handle, "If the values are not available or only ZIP is available then I do not want to populate hyphen "-".?

case when ZIP IS NULL or ' ' then ZIP||ZIP_PLUS
when ZIP_PLUS IS NULL or ' ' then ZIP||ZIP_PLUS
else ZIP||'-'||ZIP_PLUS
end as PRO_ZIP

Not sure what question you are asking. Are you saying the code you provided does not work? Are you saying it does something but not what you want? Are you asking something else?

What are the correct answers for these cases?

1. ZIP=53408 ZIP_PLUS=

2. ZIP= ZIP_PLUS=3483

3. ZIP=53408 ZIP_PLUS=3483

4. ZIP= ZIP_PLUS=

Re: Case when to handle blank and NULL values

Quentin — Sun, 04 Feb 2024 17:47:31 GMT

This could be a good use-case for the CATX function:

data want ;
  input zip $5. zip_plus $4.;

  length want $10 ;
  want=catx('-',zip,zip_plus) ;

  cards ;
12345 1234
.     1234
12345 .
;

proc print ;
run ;

Returns:

Obs     zip     zip_plus    want

 1     12345      123       12345-123
 2                123       123
 3     12345                12345

But your interpretation of the specification seems surprising. If zip is missing, I don't think there is value in having the value for zip_plus. So for row2, I would want the result be blank (because I don't know the actual zip code), rather than '123'.

Re: Case when to handle blank and NULL values

Tom — Sun, 04 Feb 2024 18:17:37 GMT

Since SAS uses fixed length character variables there is no difference between missing (what SQL syntax means by NULL) and a value that is all spaces.

The first point is that the OR operator is a BINARY operator. It takes TWO arguments, one on the LEFT and one on the RIGHT. So this code is invalid (or at least illogical).

 ZIP IS NULL or  ' '

If you put in parentheses like this:

 (ZIP IS NULL) or  (' ' )

You are asking the OR operator to treat that character string as a BOOLEAN value.

If you put in parentheses like this:

 ZIP IS (NULL or ' ')

You are asking the OR operator generate BOOLEAN value (0 or 1). Since NULL and ' ' are both missing values then the result should be 0 (FALSE). So the code is testing whether ZIP is equal to zero.

Finally it looks like you are just trying to replicate the CATX() function, but perhaps without the removal of the trailing spaces from the first value.

catx('-',ZIP,ZIP_PLUS) as PRO_ZIP

But you probably do not what that when ZIP is missing.

So perhaps you want:

case when not missing(zip) then catx('-',ZIP,ZIP_PLUS) 
  else ' '
end as PRO_ZIP length=10

And if ZIP and ZIP_PLUS are NUMERIC then you might need to format them so the leading zeros don't get removed.

case when missing(zip) then ' '
     when missing(zip_plus) then put(zip,z5.)
     else put(zip,z5.)||'-'||put(zip_plus,z4.)
end as PRO_ZIP length=10

Re: Case when to handle blank and NULL values

Babloo — Mon, 05 Feb 2024 04:55:03 GMT

@Tom May I know how to tweak/extend the following code to handle both the conditions at the same time? Both Zip and Zip_Pllus is character datatype.

I want to do this, catx('-',ZIP,ZIP_PLUS) only when ZIP and/or ZIP_PLUS is missing or NULL.

case when not missing(zip) then catx('-',ZIP,ZIP_PLUS) 
  else ' '
end as PRO_ZIP length=10

Re: Case when to handle blank and NULL values

Babloo — Mon, 05 Feb 2024 04:57:44 GMT

@Quentin What if both Zip and Zip_Plus is missing?

Re: Case when to handle blank and NULL values

yabwon — Mon, 05 Feb 2024 09:54:02 GMT

https://communities.sas.com/t5/SAS-Programming/Handling-Missing-and-Null-Values/m-p/914472#M360351

Re: Case when to handle blank and NULL values

Quentin — Mon, 05 Feb 2024 13:34:13 GMT

@Babloo wrote:

@Quentin What if both Zip and Zip_Plus is missing?

The great thing about having a small test data set and example code is that when you have a "what if" question, you can add the scenario to your example and see what happens. Here I added a 4th record with Zip and Zip_Plus missing. I'll leave it to you to run the code and explore the result.

data want ;
  input zip $5. zip_plus $4.;

  length want $10 ;
  want=catx('-',zip,zip_plus) ;

  cards ;
12345 1234
.     1234
12345 .
.     .
;

proc print ;
run ;

Re: Case when to handle blank and NULL values

Tom — Mon, 05 Feb 2024 14:27:58 GMT

@Babloo wrote:

@Tom May I know how to tweak/extend the following code to handle both the conditions at the same time? Both Zip and Zip_Pllus is character datatype.

I want to do this, catx('-',ZIP,ZIP_PLUS) only when ZIP and/or ZIP_PLUS is missing or NULL.
case when not missing(zip) then catx('-',ZIP,ZIP_PLUS) 
  else ' '
end as PRO_ZIP length=10

Did you leave out a NOT? It makes no sense to want to use CATX() only when the values are missing. If either (or both) of them is missing then the hyphen is not added. So if ZIP is NOT missing and ZIP_PLUS is missing the result of CATX('-',zip,zip_plus) is just the value of ZIP, which is what people would normally use when addressing mail in that situation. When ZIP and ZIP_PLUS are both NOT missing then you get the hyphen inserted between them.

Note it makes no sense to use the value of ZIP_PLUS if the value of ZIP is missing.

Try it and see what it does.

(note MISSING and NULL mean the same thing).