BookmarkSubscribeRSS Feed
wheddingsjr
Pyrite | Level 9

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?

 

5 REPLIES 5
LinusH
Tourmaline | Level 20

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

Data never sleeps
wheddingsjr
Pyrite | Level 9

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

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
Pyrite | Level 9

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

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 5 replies
  • 554 views
  • 0 likes
  • 3 in conversation