Proc SQL full join?

[ybz12003]

Hello:

I would like to combine three tables with everything inside by ID and Place.   I am trying to use Proc SQL full join so that I don't need to sort the table.   I wrote the code below.   It seems didn't work.

proc sql;
   create table final as
   select a.*,  
             b.*,
             c.*
from test1 a
   full join test2 b on a.ID=b.ID and a.place=b.place  
   full join test3 c on a.ID=c.ID and a.place=c.place  
   where date ne . 
   order by ID place;
quit;

[utrocketeng]

something like this?

proc sql;
create table final as
select a.*,
b.*,
c.*
from
test1 a
full join test2 b on a.ID = b.ID and a.Place = b.Place
full join test3 c on a.ID = c.ID and a.Place = c.Place
where date ne .
order by ID place;
quit;

[ybz12003]

I got worning message:

WARNING: Variable id already exists on file WORK.FINAL.

WARNING: Variable place already exists on file WORK.FINAL.

[novinosrin]

Well that's exactly what rock engineer and I were discussing. You just need to list down col names rather than use *

[ybz12003]

Too many variables names to list them all, I have 900 for each table.

[novinosrin]

Switch to datastep. If you want code help, plz post a sample of HAVE and WANT

[novinosrin]

I think @utrocketeng  is in the right direction, but just concerned if date variable exists in more than one dataset, then you may have to prefix it with an alias to avoid ambiguity. 

[utrocketeng · Accepted solution]

good observation @novinosrin,

@ybz12003 you may need to add the 'a' alias to the date filter:

proc sql;
create table final as
select
a.*,
b.*,
c.*
from
test1 a
full join test2 b on a.ID = b.ID and a.Place = b.Place
full join test3 c on a.ID = c.ID and a.Place = c.Place
where a.date ne .
order by ID place;
quit;

[ybz12003]

Actually, I popped up another question.   I not only want to have the overlapped ID in all of the table but the ID is not duplicated.   How to do this?

[DBailey]

coalesce(a.id, b.id, c.id) as id

and...I think that if you have records in each table that are duplicate for id and place, then you may need to connect b.place to c.place to keep from generating a cartesian product.

[LinusH]

900 variables...just stop there. Unless you have a specific data mining task at hand, that is not a viable approach. For starters is makes programming awkward, which you just now should realize. So rethinking your data structure could be yiur6fira concern.
If you still want to go on this track, macro programming with DICTIONARY.CLOUMNS will be your best friend.

[ybz12003]

Thanks for all of your wonderful help!

[Peter_C]

I like to get more for less
Coalesce these date variables to select where only one is non-missing or "product" them to select where all are non-missing
PROC SQL ;
create table final as
select coalescec( a.id, b.id, c.id ) as abc_id
, coalescec( a.place, b.place, c.place) as abc_place
, coalesce( a.date, b.date, c.date) as abc_date
, max( a.date, b.date, c.date) as latest_date
, min( a.date, b.date, c.date) as earliest_date
, *
From
test1 a
full join test2 b on a.ID = b.ID and a.Place = b.Place
full join test3 c on a.ID = c.ID and a.Place = c.Place
where a.date * b.date *c.date is not null
(Alternatively
Where latest_date is not null)

order by abc_ID, abc_place;
quit;

Sorry this is untested as I haven't yet found a way of testing sas code on this smartphone