topic Re: Handling Missing and Null Values in SAS Programming

Handling Missing and Null Values

Babloo — Mon, 05 Feb 2024 05:01:27 GMT

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. Also I'd like to know what would be the alternative to Catx function which can be used in SQL?

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

Re: Handling Missing and Null Values

LinusH — Mon, 05 Feb 2024 07:01:11 GMT

Just and an OR conditin to your when clause.
catx ccan be replaced by the concetanaiton operator (||) in combinaiton with the trim function.

Re: Handling Missing and Null Values

yabwon — Mon, 05 Feb 2024 09:26:12 GMT

data have;
length ZIP ZIP_PLUS $ 1;
infile cards dlm="," missover;
input ZIP ZIP_PLUS;
cards;
 , 
a, 
 ,b
a,b
;
run;

proc sql;
  select
     ZIP 
    ,ZIP_PLUS
    ,IFC(cmiss(ZIP,ZIP_PLUS), " ", catx('-',ZIP,ZIP_PLUS)) as PRO_ZIP length=3
  from
    have
  ;
quit;

Bart

Re: Handling Missing and Null Values

Babloo — Mon, 05 Feb 2024 09:56:32 GMT

@yabwon @LinusH Thank you for the comments/solution. How to tweak your code if I want to do this in native database like SQL Server or Oracle? I know that it's not a right forum but still please help me if possible.

Re: Handling Missing and Null Values

yabwon — Mon, 05 Feb 2024 10:08:11 GMT

My best guess:

select
   ZIP 
  ,ZIP_PLUS
  ,CASE WHEN data_base_function_for_checking_missings(ZIP) 
             OR
             data_base_function_for_checking_missings(ZIP_PLUS) then NULL
        ELSE function_for_strings_concatenation(ZIP,'-',ZIP_PLUS)) 
    END as PRO_ZIP
from
  have
;

Bart

Re: Handling Missing and Null Values

Babloo — Mon, 05 Feb 2024 10:14:20 GMT

@yabwon @LinusH can we do something like this?

Coalesce(ZIP,'')||coalesce('-'||case when len(ZIP_PLUS)>0 then ZIP_PLUS else NULL end ,'')

Re: Handling Missing and Null Values

yabwon — Mon, 05 Feb 2024 10:21:07 GMT

Let me put it this way: Maxim 4

Bart

Re: Handling Missing and Null Values

LinusH — Mon, 05 Feb 2024 10:45:07 GMT

Not sure about your scope.

if you want to write SQL native to external DBMS, use explicit SQL pass though.

You can try yourself with different syntax to see if your query is sent down to database using implicit pass through, by using

options sastrace=',,,d' sastraceloc=saslog msglevel=i nostsuffix;

Where is your data stored, and where are you storing your result?