ERROR: A lock is not available for ED.ALL_MRN.DATA.

[wheddingsjr]

Hello all

I tried running this code and got an error I have never seen before. I looked at the posts here about it but none of the responses given seemed to have the similar scenario. I ran this code:

PROC SQL;
CREATE TABLE all_mrn AS 
SELECT *
FROM epicfin.HospitalAccount a
LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID 
LEFT JOIN ed.all_mrn m on I.PatientIdentityID  = m.mrn
WHERE PatientIdentityID IN (SELECT mrn FROM ed.all_mrn)
;QUIT;

and got this error

34         PROC SQL;
35         CREATE TABLE all_mrn AS
36         SELECT *
37         FROM epicfin.HospitalAccount a
38         LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
39         LEFT JOIN ed.all_mrn m on I.PatientIdentityID  = m.mrn
40         WHERE PatientIdentityID IN (SELECT mrn FROM ed.all_mrn)
41         ;
ERROR: A lock is not available for ED.ALL_MRN.DATA.
ERROR: A lock is not available for ED.ALL_MRN.DATA.

What I am trying to do is extract all the rows and columns from the epicfin.HospitalAccount table that corresponds with information on the ed.all_mrn table. The ed.all_mrn table only has one column and that column is MRN. The epicfin.HospitalAccount does not have that column so I had to join it with the EPICPAT.Identity that has the PatientIdentityID column which is the equivalent to MRN. Can anyone assist me on this?

[LinusH]

It seems that someone/a process has openb that tbale in update mode, thus not allowing you to read it.

[wheddingsjr]

Thanks Linus. I did not get the error message this time when i ran the code, however, I am now getting these warnings and error:

34         PROC SQL;
35         CREATE TABLE all_mrn AS
36         SELECT *
37         FROM epicfin.HospitalAccount a
38         LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
39         LEFT JOIN ed.all_mrn m on I.PatientIdentityID  = m.mrn
40         WHERE PatientIdentityID IN (SELECT mrn FROM ed.all_mrn)
41         ;
WARNING: Variable PatientID already exists on file WORK.ALL_MRN.
WARNING: Variable CommunityPhysicalOwnerID already exists on file WORK.ALL_MRN.
WARNING: Variable CommunityLogicalOwnerID already exists on file WORK.ALL_MRN.
2                                                          The SAS System                                11:24 Monday, June 14, 2021

WARNING: Variable PartnersEDWLastModifiedDTS already exists on file WORK.ALL_MRN.
WARNING: Variable EDWLastModifiedDTS already exists on file WORK.ALL_MRN.
ERROR: Sort execution failure.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
41       !  QUIT;

[SASKiwi]

There is no need to do a left join on ALL_MRM when you only want patients in this table. Simplifying your query may help.

PROC SQL;
CREATE TABLE all_mrn AS 
SELECT *
FROM epicfin.HospitalAccount a
LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID 
INNER JOIN ed.all_mrn m on I.PatientIdentityID  = m.mrn
;
QUIT;

[wheddingsjr]

Thanks SASKiwi

I tried your suggestion but still get this error:

34         PROC SQL;
35         CREATE TABLE all_mrn AS
36         SELECT *
37         FROM epicfin.HospitalAccount a
38         LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
39         INNER JOIN ed.all_mrn m on I.PatientIdentityID  = m.mrn
40         ;
WARNING: Variable PatientID already exists on file WORK.ALL_MRN.
WARNING: Variable CommunityPhysicalOwnerID already exists on file WORK.ALL_MRN.
WARNING: Variable CommunityLogicalOwnerID already exists on file WORK.ALL_MRN.
WARNING: Variable PartnersEDWLastModifiedDTS already exists on file WORK.ALL_MRN.
2                                                          The SAS System                                11:24 Monday, June 14, 2021

WARNING: Variable EDWLastModifiedDTS already exists on file WORK.ALL_MRN.
ERROR: Sort execution failure.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
41         QUIT;

[SASKiwi]

Sort execution failures tend to indicate that the volume of data being selected is too large. Are you running this on a PC or a remote SAS server? Try simplifying your query by having just one join and see how you get on. It's possible that your join criteria needs to change but without knowing what your input data looks like I can't offer any advice.