Hi All, I am using following code for the requirment given below, Present it is taking 5 hours to complete the following query because dataset size are huge. Can somebody help me with efficient SAS code which can reduce processing time. Proc sql; create table INVENTORY as select distinct a.* ,min(case when (b.PROCESS_DATE GT a.CREATION_DATE and c.MAKING_DATE GE b.PROCESS_DATE and c.MAKING_DATE LE b.PROCESS_DATE_5DAYS and c.AMOUNT LE 0) then b.PROCESS_DATE else . end) as CLEARENCE_DATE format=date9. ,min(case when (b.PROCESS_DATE GT a.CREATION_DATE and c.MAKING_DATE GE b.PROCESS_DATE and c.MAKING_DATE LE b.PROCESS_DATE_5DAYS and c.AMOUNT LE 0) then c.MAKING_DATE else . end) as INV_DATE format=date9. from MAIN_DATA as a left join PROCESSING_DATA as b <------(Has Record Count of "682152529") on a.ID = b.ID left join MAKING_DATA as c <------(Has Record Count of "374098947") on a.ID = c.ID group by a.ID, a.PART_ID order by a.ID, a.PART_ID; Quit; I have one main base dataset "MAIN_DATA" and I am joining my "MAIN_DATA" with two huge datasets called "PROCESSING_DATA" and "MAKING_DATA" 1. MAIN_DATA (This dataset has "CREATION_DATE") 2. PROCESSING_DATA (This dataset has "PROCESS_DATE" and "PROCESS_DATE_5Days" ) 3. MAKING_DATA (This dataset has "MAKING_DATE" and "AMOUNT") 1. Requirement for Identifying "Clearence Date": I want to know earliest "PROCESS_DATE" date for the following: "PROCESS_DATE" should be After "CREATION_DATE" and "MAKING_DATE" should be between "PROCESS_DATE" and "PROCESS_DATE_5Days" and "AMOUNT" should be ZERO(0) or less than ZERO(0) 2. Requirement for Identifying "Inventory Date": I want to know earliest "MAKING_DATE" date for the following: "PROCESS_DATE" should be After "CREATION_DATE" and "MAKING_DATE" should be between "PROCESS_DATE" and "PROCESS_DATE_5Days" and "AMOUNT" should be ZERO(0) or less than ZERO(0) Thanks, Ravi
... View more