Handling Missing and Null Values

[Babloo]

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

[LinusH]

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

[yabwon]

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

[Babloo]

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

[yabwon]

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

[Babloo]

@yabwon  @LinusH   can we do something like this?

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

[yabwon]

Let me put it this way: Maxim 4

Bart

[LinusH]

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?