BookmarkSubscribeRSS Feed
Dob4Die
Calcite | Level 5

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.

9 REPLIES 9
DBailey
Lapis Lazuli | Level 10

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;

Dob4Die
Calcite | Level 5

Thanks DBiley for quick response.

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

SASKiwi
PROC Star

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

Dob4Die
Calcite | Level 5

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;

DBailey
Lapis Lazuli | Level 10

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.

LinusH
Tourmaline | Level 20

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
DBailey
Lapis Lazuli | Level 10

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.

LinusH
Tourmaline | Level 20

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2293 views
  • 4 likes
  • 5 in conversation