@trevand wrote:
Fair point. I cannot copy paste my code. So I had to type it and had typos.
I encountered such copy/paste restrictions in the past connecting to a SAS managed (hosted) environment. ...until someone showed me that all that was missing was some change of the default client setting...
For your question: Might be worth to share some representative sample data and show us the desired outcome. I've created such sample data below. If doing a full join one of the questions are: What is the relationship between the two tables? Can there be many:many relationships and what do you want to happen in such cases? What's the desired result?
data have1;
input join_id h1;
datalines;
1 1
2 1
3 1
3 2
4 1
4 2
;
data have2;
input join_id h2;
datalines;
1 1
3 1
3 2
5 1
5 2
;
proc sql;
/* create table data3 as */
select
coalesce(t1.join_id, t2.join_id) as join_id,
t1.join_id as t1_join_id,
t2.join_id as t2_join_id,
case
when not missing (t1.join_id) and not missing (t2.join_id) then 3
when missing (t1.join_id) and not missing (t2.join_id) then 2
when not missing (t1.join_id) and missing (t2.join_id) then 1 else 0
end
as join_stat,
t1.h1,
t2.h2
from have1 t1 full join have2 t2
on t1.join_id=t2.join_id
;
quit;
... View more
