Date:         Sun, 15 Feb 2004 18:34:29 -0500
Reply-To:     Don Stanley <don_stanley@PARADISE.NET.NZ>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Don Stanley <don_stanley@PARADISE.NET.NZ>
Subject:      Re: SQL outer join missing values
Comments: To: Talbot Michael Katz <topkatz@MSN.COM>

I'm not aware of anything built into SQL to do this. However, faced with
this problem a while ago and not feeling like creating a mass of code to do
coalesce calls in the SQL, I wrote this macro. It was written as an aide to
writing SQL coalesce calls, and is now used quite a lot in my code. The
macro is

%macro docoalesce / parmbuff ;
%if &syspbuff = %then %goto bypass ;

/* strip leading bracket */
%let mystring = %sysfunc(substr(&syspbuff,2)) ;
/* strip end bracket */
%let l = %sysfunc(length(%bquote(&mystring))) ;
%let l = %eval(&l-1) ;
%let mystring = %sysfunc(substr(%bquote(&mystring),1,&l)) ;

%do %while (&mystring ne ) ;
    %let p1 = %sysfunc(scan(%bquote(&mystring),1,%str(,))) ;
    %let comma = %sysfunc(index(%bquote(&mystring),%str(,))) ;
    %let mystring = %sysfunc(substr(%bquote(&mystring),%eval(&comma+1))) ;

    %let p2 = %sysfunc(scan(%bquote(&mystring),1,%str(,))) ;
    %let comma = %sysfunc(index(%bquote(&mystring),%str(,))) ;
    %let mystring = %sysfunc(substr(%bquote(&mystring),%eval(&comma+1))) ;
/*
 check if any more triplets
 */
    %let comma = %sysfunc(index(%bquote(&mystring),%str(,))) ;
/*
 &comma = 0 -- no more triplets
 */
    %if &comma = 0 %then %do ;
       %let newname = &mystring ;
          coalesce( &p1 , &p2 ) as &newname
       %let mystring = ;
    %end ;
    %else %do ;
      %let newname = %sysfunc(scan(%bquote(&mystring),1,%str(,))) ;
          coalesce( &p1 , &p2 ) as &newname ,
      %let comma = %sysfunc(index(%bquote(&mystring),%str(,))) ;
      %let mystring = %sysfunc(substr(%bquote(&mystring),%eval(&comma+1))) ;
    %end ;
 %end ;
%bypass:
/*  */
%mend ;


Here is an example of how you call it. Note that it works by defining a
series of triplets which specify each item for each coalesce ...

data one ;
  x = 1 ; y=1 ; z= '1' ;
  run ;

data two ;
  x = 2 ; y=2 ; z= '2' ;
run ;
options mprint ;

proc sql ;
  create table three as
     select %docoalesce(a.x,7,x,a.y,b.y,y,a.z,'W',z)
       from one a full join two b
       on a.x = b.x ;
quit ;


You can get a bit creative with this, for example ...

data one ;
  x = 1 ; y=1 ; z= '1'; myfile= 'c:\temp\test.rtf'; thisvar = 3 ;
  run ;

data two ;
  x = 2 ; y=2 ; z= '2' ; myfile = '' ; thisvar= 7 ;
run ;

proc sql ;
  create table three as
     select %docoalesce(a.x,7,x,a.y,b.y,y,a.z,'W',z),a.myfile,%docoalesce
(a.thisvar,b.thisvar,newvar)
       from one a full join two b
       on a.x = b.x ;
quit ;

However, my greatest use of it is where I wish to keep all fields from each
table, and the names are the same, and the first table always takes
precedence if it has a value. Under those not uncommon criteria, you can
write a small piece of code to interogate dictionary tables and generate
the %docoalesce code ... which can be a real timesaver if you have many
variables

Don