To select all UNIQUE (or DISTINCT) patterns of column values from two tables A and B, where ID's in B match up to corresponding ID's in A, plus those in which no ID in A matches up to an ID in B, SAS SQL provides the A LEFT JOIN B form of the join. With one change in the SQL statement as written, you should get what you want.

create table change as select UNIQUE cause, a.descrip, order, patient, date, site, othrvars, b.english from mydata.datatab AS a LEFT JOIN mydata.desctab AS b ON a.descrip=b.descrip order by site, patient;

I have added the AS keywords prior to the aliases and put the ON under the LEFT JOIN for clarity and to emphasize the changes. Note: In the interest of providing a quick answer I have not tested the syntax. Nonetheless, if I understand your question, the LEFT JOIN ... ON solves your problem.

-------------------- Original Question ------------------------------- Date: Fri, 31 Jan 1997 09:05:40 -0600 From: "Cates, Randall C" <rccates@MKG.COM> Subject: PROC SQL question

Dear SAS-Landers

Here's a conundrum. I have two tables. One is a data table with a description code (descrip) in one column. The other is a description table with a column (english) that defines the description code. Now the description table can have multiple possible answers for one description code. When I want to create a working dataset I use Proc SQL to merge the two and I have to winnow out just one per record in the first table. So far no problem. Just use "UNIQUE" in the select statement. Like this:

create table change as select UNIQUE cause, a.descrip, order, patient, date, site, othrvars, b.english from mydata.datatab a, mydata.desctab b where a.descrip=b.descrip order by site, patient;

My problem comes in when I have records in the data table (a) where the description code (a.descrip) is blank. I have no corresponding record in the description table so Proc SQL drops these records from the output table.

Is there a way of forcing Proc SQL to pull all records of the first table regardless of whether or not there is a corresponding match in the second table, BUT keeping the uniqueness? Before anyone asks about using SAS data steps for the winnowing I should say that, for various reasons I do need to do it in Proc SQL.

Thanks in advance Randy Cates, SAS Consultant Arbor Consulting Resources, Inc. rccates@mkg.com (alternate randycates@msn.com)