Thank you very much. It worked deleted/updated about 30,000 records took less than a minute.
End up using the following:
*created records to be updated/deleted;
*number of obs to be updated if any;
data _NULL_; if 0 then set exp_2 nobs=n; call symputx('obs',n); stop; run; %put &obs;
%if &obs>0 %then %do;
Proc sql; insert into SQLLIB.table2 select ID,DateTime,TimeIn,var1,var2,var3,var4 from EXP_2; quit;
quit; %end;
**delete records using pass through:
proc sql; connect using SQLLIB; Execute(delete t1 from [DB].[schema].[table1] as t1 join [DB].[schema].[table2] as t2 on t2.TimeIn=t1.TimeIn and t1.ID=t2.ID and t1.DateTime=t2.DateTime) by SQLLIB; disconnect from SQLLIB; quit;
Word of caution: please test with select before using delete on actual data base table--end up wiping out the whole table1 in SLQ DB while testing.
... View more