Hi ,
I am working on simple PROC SQL DELETE step with WHERE clause on one of the columns . Looks like its taking forever to run
Example code:
libname sqldsn sqlsvr dsn=SQLserverDSN user=username password='password' INSERT_SQL=YES INSERTBUFF=1000 schema=dbo;
PROC SQL;
DELETE FROM SQLDSN.table_name
WHERE column1 >= value (this datetime value)
;
QUIT;
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.
Since you're using an external dbms, you might try a pass through query.
proc sql;
connect using sqldsn as csql;
execute (
delete from dbname.schemaowner.table_name
where column1 >= value
) by csql;
disconnect from csql;
quit;
Thanks DBiley for quick response.
Can I SAS macro variable as Value in WHERE clause in pass thru? Thanks .
Yes you can. Make sure you use double quotes so the value resolves: where column1 = "&MyDatetime"
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
near 'dt'.
%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;
QUIT;
SqlServer expects dates in a different format.
%let value1='2015-02-06';
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.
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.
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.
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.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.