topic Re: Proc SQL full join? in SAS Programming

Proc SQL full join?

ybz12003 — Wed, 24 Oct 2018 15:38:19 GMT

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;

Re: Proc SQL full join?

utrocketeng — Wed, 24 Oct 2018 15:33:09 GMT

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;

Re: Proc SQL full join?

novinosrin — Wed, 24 Oct 2018 15:35:48 GMT

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.

Re: Proc SQL full join?

utrocketeng — Wed, 24 Oct 2018 15:37:46 GMT

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;

Re: Proc SQL full join?

ybz12003 — Wed, 24 Oct 2018 15:42:08 GMT

I got worning message:

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

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

Re: Proc SQL full join?

novinosrin — Wed, 24 Oct 2018 15:50:26 GMT

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

Re: Proc SQL full join?

ybz12003 — Wed, 24 Oct 2018 15:51:37 GMT

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?

Re: Proc SQL full join?

ybz12003 — Wed, 24 Oct 2018 15:53:42 GMT

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

Re: Proc SQL full join?

novinosrin — Wed, 24 Oct 2018 16:05:55 GMT

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

Re: Proc SQL full join?

DBailey — Wed, 24 Oct 2018 17:06:49 GMT

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.

Re: Proc SQL full join?

LinusH — Thu, 25 Oct 2018 06:09:10 GMT

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.

Re: Proc SQL full join?

ybz12003 — Wed, 07 Nov 2018 18:49:28 GMT

Thanks for all of your wonderful help!

Re: Proc SQL full join?

Peter_C — Wed, 07 Nov 2018 19:18:03 GMT

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