topic Re: How do I do conditional deletions in an Access 2013 table from SAS? in SAS Programming

How do I do conditional deletions in an Access 2013 table from SAS?

GustavT — Wed, 21 Jun 2017 07:38:21 GMT

Hi,

I've had a simple and strange problem for a while. I cannot do conditional deletions from tables stored in Access 2013 Databases. Even though several rows fulfills the condition only exact two(!) rows become deleted when running following code (both in EG and Base).

libname access 'H:MyDataBase.accdb';

proc sql;
	delete from access.Earnings
	where AnalysisQuarter = '2016Q4';
quit;

Can I change the code in some way to actually delete all rows fullfilling the WHERE condition?

Right now I solve this in an unsatisfactory way by deleting the whole table using DROP statement which works fine, and then add back the rows that I did not wanted to delete in the first place.

Grateful for help!

Gustav

Re: How do I do conditional deletions in an Access 2013 table from SAS?

RW9 — Wed, 21 Jun 2017 07:57:21 GMT

Well, your libname statement is incorrect to start with, missing a \ after h:. As for access I really wouldn't use it for anything serious. Your SQL seems syntactically correct, so I would have to, without any further information assume that the data does not match your where clause. Is

AnalysisQuarter

A numeric or text field? Text fields coud have extra spaces in or hidden characters, numerics may not be exactly the same, for instance:

date="01JAN2017"d;
format date yymm6.;

!=

date="10JAN2017"d;
format date yymm6.;

Looking at the formatted date, both would show the same, but the underlying data is actually different.

Re: How do I do conditional deletions in an Access 2013 table from SAS?

Kurt_Bremser — Wed, 21 Jun 2017 08:09:53 GMT

Maxim 3 (Know your data).

Do a proc contents on access.earnings, and run a proc freq on AnalysisQuarter in access.earnings.

If in doubt, post the output from those procedures here.

Re: How do I do conditional deletions in an Access 2013 table from SAS?

GustavT — Wed, 21 Jun 2017 08:26:24 GMT

Thanks for response,

Sorry, you're right. Before I posted the code I removed the (long) path and happened to delete one character too much.

It is a text field, and what I know no spaces or hidden characters. Regardless of table and attribute it is allways exact two rows that become deleted.

Re: How do I do conditional deletions in an Access 2013 table from SAS?

GustavT — Wed, 21 Jun 2017 08:34:53 GMT

Thanks for response,

Unfortunately I do still have doubts, please see output. Do you have any idea of what is wrong?

proc contents data=access.Earnings position; run;
proc freq data=access.Earnings; tables AnalysisQuarter; run;

Data Set Name	ACCESS.Earnings	Observations	.
Member Type	DATA	Variables	7
Engine	ACCESS	Indexes	0
Created	.	Observation Length	0
Last Modified	.	Deleted Observations	0
Protection		Compressed	NO
Data Set Type		Sorted	NO
Label
Data Representation	Default
Encoding	Default

Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label

1	AnalysisQuarter	Char	255	$255.	$255.	AnalysisQuarter
3	Country	Char	255	$255.	$255.	Country
5	Earning_Year	Num	8	11.	11.	Earning_Year
4	HRG	Char	255	$255.	$255.	HRG
2	ResQTree	Char	255	$255.	$255.	ResQTree
7	Time	Num	8	DATETIME19.	DATETIME19.	Time
6	Value	Num	8			Value

AnalysisQuarter AnalysisQuarter Frequency Percent Cumulative
Frequency Cumulative
Percent

2015Q4	20	66.67	20	66.67
2016Q4	10	33.33	30	100.00

Regards,

Gustav

Re: How do I do conditional deletions in an Access 2013 table from SAS?

GustavT — Wed, 21 Jun 2017 08:47:05 GMT

Hi again,

The strange thing is that if I run the code again, two more rows become deleted... So, the data do match the WHERE clause I beleive.

Regards,

Gustav

Re: How do I do conditional deletions in an Access 2013 table from SAS?

Kurt_Bremser — Wed, 21 Jun 2017 08:47:30 GMT

Interesting.

What do you get when you do a simple

proc sql;
select * from access.Earnings where AnalysisQuarter = '2016Q4';
quit;

I somehow suspect that SAS hands part of the SQL off to access (implicit pass-through) and the delete's are botched on the way.

So my immediate workaround would be to pull the table into SAS, manipulate it there and copy it back into the Access DB.

Re: How do I do conditional deletions in an Access 2013 table from SAS?

RW9 — Wed, 21 Jun 2017 08:48:31 GMT

Mmm, well, lets sort out the syntax first:

libname temp access 'H:\MyDataBase.accdb' dbcommit=1000;

proc sql;
  delete from temp.Earnings
  where strip(AnalysisQuarter)='2016Q4';
quit;

All I can think of is the dbcommit option I show above, but why that would be set to 2 I don't know, docs:

https://support.sas.com/documentation/cdl/en/acpcref/69731/HTML/default/viewer.htm#p1gbem6un8cr3jn10ppxkt0imtdu.htm

Maybe try some of the other options if not.

Re: How do I do conditional deletions in an Access 2013 table from SAS?

GustavT — Wed, 21 Jun 2017 08:57:32 GMT

Hi again,

When I run the code you posted I get all ten rows that fulfill AnalysisQuarter = '2016Q4'. So no hidden spaces or other characters I beleive.

Yes, that is another solution. As a mentioned I already have a walkaround. The problem with both your and mine workaround is that Access will treat it as a "new table" and possible grouping of tables within Access get lost, which is a bit annoying.

As a I mentioned when I answered @RW9 two more rows become deleted when I run the code one more time. Strange...

Regards,

Gustav

Re: How do I do conditional deletions in an Access 2013 table from SAS?

GustavT — Wed, 21 Jun 2017 09:14:04 GMT

Thank you!

When using dbcommit I cannot do any deletions, but I tried with

proc sql;
	delete from access.Earnings
	where strip(AnalysisQuarter) = '2016Q4';
quit;

without dbcommit and it worked!

So, problem solved but I am still confused. The fact that the code above works means that all '2016Q4' rows are not equal to '2016Q4'. But as I said, I succeed to delete all rows with the initial code when running it five times (5*2=10)...

Any ideas why?

Thank you,

Gustav

Re: How do I do conditional deletions in an Access 2013 table from SAS?

Kurt_Bremser — Wed, 21 Jun 2017 09:21:27 GMT

<irony>

Keep in mind that your backend here is a Microsoft product, which means that it is some kind of sophisticated random generator for outcomes.

And it only works perfectly in odd leapyears.

</irony>

Re: How do I do conditional deletions in an Access 2013 table from SAS?

RW9 — Wed, 21 Jun 2017 09:30:23 GMT

Sorry, without some in depth analysis of the file and the SQL generated behind the scenes I couldn't say. I do however agree with @Kurt_Bremser in that MS products are really bad from a programmer/data perspective. Sure they are easy learning curve in, and great for entering data, but for any real task use a real application. SQLite for embedded, MySQL or one of the free ones, or in a commercial venture get a paid DB like Oracle.

You could pop a ticket into support if you really want to find out, just add the file, show the code and issue.

One other thing, does the code run directly in Access ok? If so we know its the passthrough conversion.

Re: How do I do conditional deletions in an Access 2013 table from SAS?

GustavT — Wed, 21 Jun 2017 10:46:36 GMT

Thank you both, @RW9 @Kurt_Bremser. And thanks for sharing your software thoughts.

Best regards,

Gustav