PROC SQL & DELETE

[_LB]

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 · Accepted solution]

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

[_LB]

Thanks Poli!

[_LB]

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]

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]

OK...WIll take a look. Thanks.

[_LB]

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