topic Re: Why is my program throwing a LOCK error and how do I fix it? in New SAS User

Why is my program throwing a LOCK error and how do I fix it?

adamsfam1 — Fri, 05 Oct 2018 15:54:57 GMT

Please help me! Why is my program throwing this lock error and how do I fix it? Everything is working fine in my program until I get to this section. I'm working in SAS EG 6.1. My output will consist of both files, so I need to write both out to nonWORK files. Please see the section from my log below

PROC SQL;
66 create table jmada2.&acct._member_list as
67 select distinct
68 CASE
69 when svc_period = '1' then 'BASE'
70 when svc_period = '2' then 'CURR'
71 else ''
72 END
73 as svc_period,
74 mbr_num,
75 category_cd as condition
76 from datasrc.cp_hev2_acty
77 where svc_period in ('1','2') and
78 identified_ind = 'Y' and
79 category_cd in
79 ! ('DIABETES','ASTHMA','CORONARY_ARTERY_DISEASE','CONGESTIVE_HEART_FAIL
79 ! URE','CHRONIC_OBSTRUCTIVE_PULMONARY_DISEASE')
80 and &STRUC_KEY_FILTER.
81 ;
NOTE: SAS threaded sort was used.
NOTE: Table JMADA2.JCPENNEY_MEMBER_LIST created, with 8053 rows and 3 columns.

82 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 8.11 seconds
user cpu time 0.35 seconds
system cpu time 0.19 seconds
memory 9104.00k
OS Memory 28672.00k
Timestamp 10/05/2018 11:36:51 AM
Step Count 29 Switch Count 128
Page Faults 414
Page Reclaims 2390
Page Swaps 0
Voluntary Context Switches 523
Involuntary Context Switches 67
Block Input Operations 0
Block Output Operations 0

4 The SAS System 11:35 Friday, October 5, 2018

83
84 PROC SQL;
85 create table jmada2.&acct._calls as
86 select
87 CASE
88 when svc_period = '1' then 'BASE'
89 when svc_period = '2' then 'CURR'
90 else ''
91 END
92 as svc_period,
93 mbr_num,
94 count(distinct fact_beg_dt) as call_count
95 from datasrc.cp_hev2_acty
96 where mbr_num in (select distinct mbr_num from
96 ! jmada2.&acct._member_list) and
97 coaching_call_ind = 'Y'
98 and &STRUC_KEY_FILTER.
99 group by 1, 2
100 ;
ERROR: A lock is not available for JMADA2.JCPENNEY_CALLS.DATA.
ERROR: Lock held by process 22806732.

Re: Why is my program throwing a LOCK error and how do I fix it?

SuryaKiran — Fri, 05 Oct 2018 16:01:52 GMT

The dataset might be opened by some other user or might be by you.

Try LOCK JMADA2.JCPENNEY_CALLS CLEAR; If it is locked by you.

When running the above, if you get It is not locked by you then you might ask the person who opened the dataset to close it.

Re: Why is my program throwing a LOCK error and how do I fix it?

ballardw — Fri, 05 Oct 2018 16:12:43 GMT

I would check to ensure that your Proc SQL involving that step actually has a quit to end it.

Unable to get a Lock often indicates you or another person/program is using the file in some manner. So if another program might be running attempting to read or write that data set in that library you could get that message.

Re: Why is my program throwing a LOCK error and how do I fix it?

Kurt_Bremser — Fri, 05 Oct 2018 17:01:24 GMT

Do a ps -fp 22806732 on the server to find out who owns the process that's getting in your way.