SAS Sql code for returning matches

[RW9]

Well question 1 would then be, if you have duplicates, which of the duplicates you want to return the result of?  Is it by date, sequence number etc.  Once you identify what you want your single record to be then update the inner select to only select that row, maybe you just want

proc sql noprint;

     create table WANT as

     select     A.*,

                   COALESCE((select THIS.STATUS from TABLEB THIS where A.ID=THIS.ID and THIS.DATE=(select max(B.DATE) from TABLEB B where B.ID=THIS.ID)),"N/a") as AVAILABILITY

     from          TABLEA A;

quit;#

Again it is exactly the same thing with a left join. Just clearly identify in your from clause what data should be coming into the join, eliminate duplicates at that stage, for instance if you assume that sold is more important then available do:

from  (select ID,MAX(case when STATUS="sold" then 2 else 1 end) as MAX_STATUS from TABLEA group by ID)

This will then give one record per ID with sold preferred above available.