Date:         Thu, 12 Jul 2007 12:55:01 -0400
Reply-To:     Mike Rhoads <RHOADSM1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Rhoads <RHOADSM1@WESTAT.COM>
Subject:      Re: PROC SQL: Drop double variables
Comments: To: Will <schimpanski@gmx.de>
In-Reply-To:  <5fmsduF3cfkitU1@mid.individual.net>
Content-Type: text/plain; charset="us-ascii"

If you only want rows that match, and if the only variables with
duplicate names are your "join" variables, a NATURAL JOIN is probably
the way to go:

create table ab as
select * from a natural join b;

(Thanks to Howard Schreier for bringing this to my attention last year.)



-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Will
Sent: Thursday, July 12, 2007 10:35 AM
To: sas-l@uga.edu
Subject: PROC SQL: Drop double variables


Hi,

I'd like to join/merge two large tables a and b where b contains a
subset of the variables in a (e.g. tid) and other variables.

Example:

Table a                 Table b
------------            --------------
id var1 var2            id var1 var3
1   3.4 10.2            1   3.4 20.1
2   2.9 11.3            2   2.9 21.3
3   3.1 13.7            3   3.1 23.5
4   4.2 10.3            4   4.2 21.1

Table ab
-----------------
id var1 var2 var3
1   3.4 10.2 20.1
2   2.9 11.3 21.3
3   3.1 13.7 23.5
4   4.2 10.3 21.1


How can I merge those tables with PROC SQL so the double
columns/variables in b (e.g. id, var1) are dropped _without_ referring
to them in the command in an explicit way? (N.B. As I have large
datasets with several hundred variable explicitly selecting the
variables would be quite a lot of work.)

At the moment the double variables are kept in the joined dataset which
causes problems when doing an "outer union corr" concatenation with
another third table.

Thanks in advance,

Will