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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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

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

 


Accepted Solutions
Solution
‎06-21-2017 06:46 AM
Occasional Contributor
Posts: 7

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

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

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,207

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

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. 

Occasional Contributor
Posts: 7

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

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.

Occasional Contributor
Posts: 7

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

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

Esteemed Advisor
Posts: 6,648

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

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

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 NameACCESS.EarningsObservations.
Member TypeDATAVariables7
EngineACCESSIndexes0
Created.Observation Length0
Last Modified.Deleted Observations0
Protection CompressedNO
Data Set Type SortedNO
Label   
Data RepresentationDefault  
EncodingDefault  

 

Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label
1AnalysisQuarterChar255$255.$255.AnalysisQuarter
3CountryChar255$255.$255.Country
5Earning_YearNum811.11.Earning_Year
4HRGChar255$255.$255.HRG
2ResQTreeChar255$255.$255.ResQTree
7TimeNum8DATETIME19.DATETIME19.Time
6ValueNum8  Value

 

AnalysisQuarter AnalysisQuarter Frequency Percent Cumulative
Frequency Cumulative
Percent
2015Q42066.672066.67
2016Q41033.3330100.00

 

Regards,

 

Gustav

Esteemed Advisor
Posts: 6,648

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

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

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,207

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

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#p1gbem6un8cr3jn10...

Maybe try some of the other options if not. 

Solution
‎06-21-2017 06:46 AM
Occasional Contributor
Posts: 7

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

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

Esteemed Advisor
Posts: 6,648

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

<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>

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,207

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

[ Edited ]

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 @KurtBremser 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.

Occasional Contributor
Posts: 7

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

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

 

Best regards,

Gustav

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 132 views
  • 1 like
  • 3 in conversation