@BCNAV
I prefer what @Astounding proposes but just for "completeness":
You can use the coalesce() function in SQL for both numerical and character variables.
If you've got a lot of variables then you could generate the SELECT clause using the dictionary tables. It complicates the code so I'd only not just type but generate the select clause if it saves a lot of typing.
Below code demonstrates how code generation could look like. Below also works if not all variables exist in both source data sets.
/* create sample data */
data have1;
otherVar_h1_1=_n_;
set sashelp.class;
if _n_=2 then call missing(sex);
if _n_=3 then call missing(age, height);
otherVar_h1_2=_n_;
run;
data have2;
otherVar_h2_1=_n_;
set sashelp.class;
if _n_=2 then call missing(age, height);
if _n_=3 then call missing(sex);
otherVar_h2_2=_n_;
run;
/* genereate Select clause using coalesce() */
proc sql noprint;
select
case
when missing(h1.name) then h2.name
when missing(h2.name) then h1.name
else
catx( ' ',
cats('coalesce(',h1.memname,'.',h1.name,',',h2.memname,'.',h2.name,') as'),
h1.name,'length=',put(max(h1.length,h2.length),5.)
)
end
into :SelectClause separated by ','
from
(
select name, varnum, memname, length
from dictionary.columns
where libname='WORK' and memname='HAVE1'
) h1
full outer join
(
select name, varnum, memname, length
from dictionary.columns
where libname='WORK' and memname='HAVE2'
) h2
on upcase(h1.name)=upcase(h2.name)
order by coalesce(h1.varnum, h2.varnum)
;
quit;
/*%put &=SelectClause;*/
/* combine tables */
proc sql feedback;
select
&SelectClause
from
have1 t1 full outer join have2 t2
on t1.name=t2.name
;
quit;
... View more
