DATA Step, Macro, Functions and more

PROC SQL DELETE on seletive columns taking forever.

Reply
Occasional Contributor
Posts: 9

PROC SQL DELETE on seletive columns taking forever.

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.

Super Contributor
Posts: 578

Re: PROC SQL DELETE on seletive columns taking forever.

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;

Occasional Contributor
Posts: 9

Re: PROC SQL DELETE on seletive columns taking forever.

Thanks DBiley for quick response.

Can I SAS macro variable as Value in WHERE clause in pass thru? Thanks .

Super User
Posts: 3,104

Re: PROC SQL DELETE on seletive columns taking forever.

Yes you can. Make sure you use double quotes so the value resolves: where column1 = "&MyDatetime"

Occasional Contributor
Posts: 9

Re: PROC SQL DELETE on seletive columns taking forever.

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;

Super Contributor
Posts: 578

Re: PROC SQL DELETE on seletive columns taking forever.

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.

Super User
Posts: 5,256

Re: PROC SQL DELETE on seletive columns taking forever.

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.

Data never sleeps
Super Contributor
Posts: 578

Re: PROC SQL DELETE on seletive columns taking forever.

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.

Super User
Posts: 5,256

Re: PROC SQL DELETE on seletive columns taking forever.

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.

Data never sleeps
Super User
Super User
Posts: 7,401

Re: PROC SQL DELETE on seletive columns taking forever.

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?

Ask a Question
Discussion stats
  • 9 replies
  • 697 views
  • 4 likes
  • 5 in conversation