topic PROC SQL & DELETE in SAS Programming

PROC SQL & DELETE

_LB — Thu, 15 Dec 2011 16:47:58 GMT

hanks.

Hello all-

Have a basic question on PROC SQL-

When I run a delete query such as below:

proc sql;

delete from HH.hh_TEST

where Month_and_Year_of_Observation ='NOV 2011';

QUIT;

It only erases two entries at a time instead of all 300. It is running off an Access DB if that makes any difference.

How can I erase all 300 w/o necessary running a loop.

And why does it do this..

Thanks.

Lawrence

polingjw — Thu, 15 Dec 2011 16:53:40 GMT

Look at using the DIRECT_EXE = DELETE option on the LIBNAME statement.

_LB — Thu, 15 Dec 2011 17:24:04 GMT

Thanks Poli!

_LB — Mon, 23 Jan 2012 17:37:34 GMT

Poli-

OK-I have used the the direct DIRECT_EXE = DELETE option with access 2003.

DO you lnow the provider for Access 2010?

I have the string

libname T oledb provider="Microsoft.Jet.OLEDB.4.0"

properties=('data source'='X:\X.MDB') DIRECT_EXE = DELETE but the provider is not correct for access 10-

I went online and found a different "provider- but that fails too.

Thanks.

Lawrence

polingjw — Mon, 23 Jan 2012 17:51:03 GMT

I don't have any Access 2010 applications to test on. Problem Note 9731 on the SAS support site might be of some help to you.

_LB — Mon, 23 Jan 2012 17:52:34 GMT

OK...WIll take a look. Thanks.

_LB — Mon, 23 Jan 2012 18:03:44 GMT

FYI:

The replacement is Microsoft.ACE.OLEDB.12.0 for access 2007/2010

from Microsoft.JET.OLEDB.4.0 for access 2003

in case you migrate up-which I don't recommend!

Lawrence