BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GustavT
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
GustavT
Fluorite | Level 6

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

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

GustavT
Fluorite | Level 6

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.

GustavT
Fluorite | Level 6

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

Kurt_Bremser
Super User

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.

GustavT
Fluorite | Level 6

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

Kurt_Bremser
Super User

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.

GustavT
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

GustavT
Fluorite | Level 6

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

Kurt_Bremser
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

GustavT
Fluorite | Level 6

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

 

Best regards,

Gustav

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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