DATA Step, Macro, Functions and more

PROC SQL & DELETE

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 170
Accepted Solution

PROC SQL & DELETE

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


Accepted Solutions
Solution
‎12-15-2011 11:53 AM
Regular Contributor
Posts: 171

PROC SQL & DELETE

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

View solution in original post


All Replies
Solution
‎12-15-2011 11:53 AM
Regular Contributor
Posts: 171

PROC SQL & DELETE

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

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL & DELETE

Thanks Poli!

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL & DELETE

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

Regular Contributor
Posts: 171

PROC SQL & DELETE

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.

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL & DELETE

OK...WIll take a look. Thanks.

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL & DELETE

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 238 views
  • 0 likes
  • 2 in conversation