02-05-2015 05:14 PM
I am working on simple PROC SQL DELETE step with WHERE clause on one of the columns . Looks like its taking forever to run
libname sqldsn sqlsvr dsn=SQLserverDSN user=username password='password' INSERT_SQL=YES INSERTBUFF=1000 schema=dbo;
DELETE FROM SQLDSN.table_name
WHERE column1 >= value (this datetime value)
without WHERE clause its running in fraction of second and same WHERE clause working quick in SELECT * query but DELETE from table taking forever . Looking for thoughts what causing PROC SQL DELETE taking forever.
Tried direct_exe=DELETE in LIBNAME statement but not improvement.
Thanks for your help .
SAS version . 9.4 on SAS GRID.
02-05-2015 05:36 PM
Since you're using an external dbms, you might try a pass through query.
connect using sqldsn as csql;
delete from dbname.schemaowner.table_name
where column1 >= value
) by csql;
disconnect from csql;
02-06-2015 12:05 AM
I am doing something wrong here ...I am keep getting below error:
ERROR: CLI execute error: [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax
%let value1 = "06FEB2015:00:00:00.000"dt;
PROC SQL ;
CONNECT TO SQLSVR AS sqldsn(datasrc=SQLDBDSN user=USERNAME password='PASSWORD');
EXECUTE(DELETE FROM DATBASE.DBO.table_name
WHERE column1>= "&value1") by sqldsn;
DISCONNECT FROM SQLDSN;
02-06-2015 09:00 AM
SqlServer expects dates in a different format.
You can't use double quotes. If this is in a macro, you'd need to use %bquote or some other function to force the evaluation of the macro variable.
02-06-2015 10:03 AM
Doing explicit pas thru is making it DBMS dependant and unnecessary complicated.
SAS/ACCESS is capable of transforming date and date-time constants on the fly.
02-10-2015 09:57 AM
Don't disagree...but the original request was to improve performance on the approach you mentioned. This is the most common way of doing that.
02-10-2015 11:25 AM
OP was in pseudo-code, so it's hard to see what was causing the initial slow performance.
Adding OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG; might give some clues on what gets pushed down and how.
Also, see the DIRECT_EXE libname option.
02-06-2015 10:06 AM
Just out of interest, why are you performing admin tasks on a database from SAS? Would you not be better served addressing DB admin tasks via the DB itself?