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