PROC SQL update query raising relational integrity error

[elisesmara]

Been scratching my head at this one and wondering whether anyone else has encountered something like it--

I've been trying to use SAS to update a field in an MS Access data table--let's call it tblFacility. This table has a two-part primary key made up of variables person and site. It is linked to another table, tblIdentifier, which has a three-part primary key made up of person, site and ID. Person and site in tblIdentifier reference person and site in tblFacility, and relational integrity is enforced.

I'm trying to update testdate, a date field in tblFacility that does not reference any other table. This is what the relationships look like:

Here is my code. ("fi" is a SAS dataset; libname "ptdb" refers to the DSN pointing at the database):

PROC SQL;
	update ptdb.tblFacility as a
	set testdate = (select dhms(b.testdate,0,0,0) as testdate
				from fi as b
				where b.person = a.person and b.site = a.site and dhms(b.testdate,0,0,0) > dhms(a.testdate,0,0,0))
		where exists (
			select 1
			from fi as b
			where b.person = a.person and b.site = a.site and dhms(b.testdate,0,0,0) > dhms(a.testdate,0,0,0))
	;
QUIT;

When I try to run this, it fails with the following error:

ERROR: Error updating table entry: [Microsoft][ODBC Microsoft Access Driver] The record cannot be deleted or changed because table 'tblIdentifier' includes related records.

I've found that I can bypass this error by switching on the "cascade update" option for the relationship between these two tables, but I'm frankly mystified as to why it's being raised at all, given that my subquery isn't supposed to be touching the key variables. Am I missing something here? Is there a way to modify this update query to avoid this issue?

[PGStats]

The problem might be the reference to a.testdate in the exists clause. Try

PROC SQL;
	update ptdb.tblFacility as a
	set testdate = coalesce(
				(select dhms(testdate,0,0,0)
				from fi
				where 
					person = a.person and 
					site = a.site and 
					testdate > datepart(a.testdate)),
				testdate)
	;
QUIT;

P.S. As a separate issue, your use of dhms() might be wrong. The first argument to the function should be a SAS date. But testdate in your Access database will most likely map to a SAS datetime. So dhms should be applied only to the value of testdate in dataset fi.

[elisesmara]

Thanks for the suggestion re: coalesce--unfortunately it didn't work 😞

I did a bit more exploration and I have a strong suspicion that SAS is doing something to those key variables during execution of the subquery that is bumping up against the integrity constraint. For instance, if I limit testdate to one value (and cut out some of the other things for simplicity's sake), any construction that references person and/or site in the WHERE clause fails:

PROC SQL;
	update ptdb.tblFacility as a
	set testdate = (select max(testdate)
				 from fi as b
				where b.person = a.person)
        ;
QUIT;

But if I alter the WHERE clause so that it refers only to some other variable in tblFacility that isn't referenced by tblIdentifier, e.g. "otherdate" below, it runs:

PROC SQL;
	update ptdb.tblFacility as a
	set testdate = (select max(testdate)
				from fi as b
				where b.testdate > a.otherdate)

	;
QUIT;

re: dhms() yes, my mistake--it's needed for exporting a date to Access but not reading it in to SAS.