My soultion is very simple. The only changes I made are in caps.

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

Pay attention to the caps. Outer joins keep the rows that do not match with any row from the table MYDATA.DESCTAB. Read SAS/SQL manual.

Haiou

---------- From: Cates, Randall C To: Multiple recipients of list SAS-L Subject: PROC SQL question Date: Friday, January 31, 1997 9:05AM

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)