How do I stop "A lock is not available for <dataset>" errors in SAS? -

i consistently "a lock not available" errors when running sas programs. happens if perform operations on same dataset multiple times in 1 program. after researching error, it's understanding means 2 programs trying access same dataset. in other words, it's similar trying open document in use else or yourself. here example of code giving me error:

data tstone.map;     infile <path> delimiter = ',' truncover firstobs=2 dsd termstr=cr lrecl=32760;      format assessment_edition $45.;     format ...      input     assessment_edition :$45.     ...     ; run;  data tstone.map;     set tstone.map;     drop districtname ...; run; 

i entered "..." in few places have long lists of fields import or drop. so, first i'm imported csv file, , performing data step overwrite file dropping fields don't need. should note when run programs not lock errors. no other users accessing these datasets, local machine. also, if highlight , run 2 data steps sequentially no issues.

edit 10/8/2015: macros in answer updated 10/8/2015. better debugging info, added options terminate sas nicely regardless of whether running in batch or interactive mode.

edit 12/8/2014: macros in answer updated 12/8/2014. if have version prior notice slow lock times if libname contains hundreds or thousands of datasets. updated versions below have fixed issue.

answer : have datasets updated every many minutes, , @ same time need adhoc , scheduled reports access datasets. overcome locking issues created macros lock , unlock tables prior using them. it's been running without reported errors year (we've ironed out bugs we've encountered anyway).

usage:

program in session a:

%lock(ids=sashelp.class); ** read table; %unlock(ids=sashelp.class); 

program in session b:

%lock(ids=sashelp.class); ** update table; %unlock(ids=sashelp.class); 

how works.... let's session b launches first. lock dataset prior performing update. session wants use same table (while b still updating) it. before session tries it, check see if locked. if locked session wait period of time (default=5mins) before deciding give up. if session b finishes within 5 minutes, release lock , session lock , continue none-the-wiser. session unlock when done. there's bunch of other options can pass in necessary customize how handle things when lock can't attained.

the %lock macro:

/****************************************************************************** ** program:  macro.lock.sas **              ** description: locks sas table can need do. **  ** parameters: ids     :             table try , lock. **             itimeout:             maximum # of seconds wait trying lock. **             iretry  :             how retry getting lock (in seconds) **             iverbose:             whether print out debugging info **             iendquietlyontimeout: exit sas if lock fails **             iignoreerror:         ignore errors if dataset doesnt exist , cant locked ** ******************************************************************************* ** version: ** 2.0 on: 13-nov-14 by: rp **     changed method of checking whether file exists use fileexist  **     function.  betterer. ** 2.1 on: 14-sep-15 by: ms **     added optional flag ignore errors while attempting lock. bettererer. ** 2.2 on: 25-sep-15 by: rp **     handled fails bettererer.  made words in log more bettererer too. ******************************************************************************/  %macro lock(ids=, itimeout=600, iretry=3, iverbose=1, iendsasquietlyontimeout=0, iignoreerror=0);    %global lock_lock_failed ;   %local starttime lib mbr physical_filename;    %let starttime = %sysfunc(datetime());   %let lock_lock_failed = 1;    /*   ** make sure required ds not view.   ** todo. change accept 1 word data set references   */   %let lib               = %sysfunc(pathname(%sysfunc(scan(&ids,1))));   %let mbr               = %sysfunc(scan(&ids,2));   %let physical_filename = &lib\&mbr..sas7bdat;       %if not %sysfunc(fileexist(&physical_filename)) %then %do;     %if not &iignoreerror %then %do;       %put &err: (macro.lock.sas) dataset tried lock not exist (or tried lock view not possible). ;       %put &err: (macro.lock.sas) dataset name &ids .  exiting sas.;       %stop_sas;     %end;     %else %do;       %put note: (macro.lock.sas) dataset tried lock not exist (or tried lock view not possible). ;       %put note: (macro.lock.sas) continuuing despite failure lock iignoreerror=&iignoreerror ;           %end;   %end;   %else %do;      %do %until(&lock_lock_failed eq 0 or %sysevalf(%sysfunc(datetime()) gt (&starttime + &itimeout)));        %if &iverbose %then %do;         %put trying open ...;       %end;        data _null_;         dsid = 0;         until (dsid gt 0 or datetime() gt (&starttime + &itimeout));           dsid = open("&ids");           if (dsid eq 0) do;             rc = sleep(&iretry);           end;         end;         if (dsid gt 0) do;           rc = close(dsid);         end;       run;        %if &iverbose %then %do;         %put trying lock ...;       %end;        lock &ids;        %if &syslckrc eq 0 %then %do;         %let lock_lock_failed = 0;       %end;       %else %do;         /*         ** happen when dataset being viewed in interactive session.         ** open function able open dataset lock function         ** fail.  when happens sleep here dont thousands of lock          ** attempts in log file.         */         %let rc = %sysfunc(sleep(%eval(&iretry * 5)));       %end;        %if &iverbose %then %do;         %put syslckrc=&syslckrc;       %end;      %end;      %if &lock_lock_failed %then %do;       %if &iendsasquietlyontimeout %then %do;         %stop_sas;       %end;       %put &err: (macro.lock.sas) not lock dataset before timeout (&itimeout) occurred.;     %end;    %end;  %mend; 

the unlock macro:

/****************************************************************************** ** program:  macro.unlock.sas **              ** description: unlocks sas table locked macros.lock.sas. **  ** parameters: ids     : table try , unlock. ** ******************************************************************************* ** version: ** 1.0 on: 07-oct-11 by: rp **     created. ******************************************************************************/  %macro unlock(ids=);   /*    ** unlock if lock successful   */   %if %symexist(lock_lock_failed) %then %do;     %if &lock_lock_failed eq 0 %then %do;       lock &ids clear;     %end;     %else %do;       %put attempt unlock ignored attempt lock failed.;     %end;   %end;   %else %do;     %put attempt unlock ignored no attempt lock made.;   %end; %mend; 

these programs require other utility macros exist.

isdir macro:

/****************************************************************************** ** program:  cmn_mac.isdir.sas ** ** description: determines if specified path exists or not. **              returns: 0 if path not exist or not opened. **                       1 if path exists , can opened. ** ** parameters: ipath: full path examine.  note / , \ treated **                    same &sasdir/common/macros same **                    &sasdir\common\macros. ** ******************************************************************************* ** version: ** 1.0 on: 13-jul-07 by: rp **     created.   ** 1.1 on: 29-apr-10 by: rp **     added cleanup code files not remain locked. ** 1.2 on: 15-apr-14 by: jg **     added more debugging info. ** 1.3 on: 12-dec-14 by: rp **     cleaned debugging info single line ******************************************************************************/  %macro isdir(ipath=,iquiet=1);   %local result dname did rc;    %let result = 0;   %let check_file_assign =  %sysfunc(filename(dname,&ipath));    %put assigned fileref (0=yes, 1=no)? &check_file_assign &ipath;     %if not &check_file_assign %then %do;      %let did = %sysfunc(dopen(&dname));      %if &did %then %do;       %let result = 1;     %end;     %else %if not &iquiet %then %do;       %put &err: (isdir macro).;       %put %sysfunc(sysmsg());     %end;      %let rc = %sysfunc(dclose(&did));    %end;   %else %if not &iquiet %then %do;     %put &err: (isdir macro).;     %put %sysfunc(sysmsg());   %end;    &result  %mend; /*%put %isdir(ipath=&sasdir\commonn\macros);*/ /*%put %isdir(ipath=&sasdir/kxjfdkebnefe);*/ /*%put %isdir(ipath=&sasdir/kxjfdkebnefe, iquiet=0);*/ /*%put %isdir(ipath=c:\temp);*/ 

filelist macro:

/****************************************************************************** ** program:  macro.file_list.sas ** ** description: returns list of files in directory seperated **              specified delimiter. returns empty string if the  **              directory can't read or not exist. ** ** parameters: ipath: full path examine.  note / , \ treated **                    same &sasdir/common/macros same **                    &sasdir\common\macros. works both unix , windows. ** ******************************************************************************* ** version: ** 1.0 on: 17-jul-07 by: rp **     created.   ** 1.1 on: 29-apr-10 by: rp **     added cleanup code files not remain locked. **     fixed error occurring when ipath did not exist. ** 1.2 on: 18-aug-10 by: rp **     catered macro chars in filenames ** 1.3 on: 14-apr-14 by: rp&jg **     added more debugging info check path ** 1.4 on: 13-nov-14 by: rp **     changed program flow make both easier read ,  **     reduce unnecessary checks / improve performance. ******************************************************************************/ /* ** todo. theres 100 ways improved.  sometime. ** if statements filters. */ %macro file_list(ipath=, ifilter=, ifiles_only=0, idelimiter=|);   %local result did dname cnt num_members filename rc check_dir_exist check_file_assign;    %let result=;    %let check_dir_exist = %isdir(ipath=&ipath);   %let check_file_assign = %sysfunc(filename(dname,&ipath));    %put desired path:  &ipath;   %if &check_dir_exist , not &check_file_assign %then %do;      %let did = %sysfunc(dopen(&dname));     %let num_members = %sysfunc(dnum(&did));      %do cnt=1 %to &num_members;        %let filename = %qsysfunc(dread(&did,&cnt));       %if "&filename" ne "" %then %do;          %if "&ifilter" ne "" %then %do;           %if %index(%lowcase(&filename),%lowcase(&ifilter)) eq 0 %then %do;             %goto next;           %end;         %end;          %if &ifiles_only %then %do;           %if %isdir(ipath=%nrbquote(&ipath/&filename)) %then %do;             %goto next;           %end;         %end;          %let result = &result%str(&idelimiter)&filename;          %next:        %end;       %else %do;         %put error: (cmn_mac.file_list) file cannot read.;         %put %sysfunc(sysmsg());       %end;     %end;      %let rc = %sysfunc(dclose(&did));    %end;   %else %do;      %put error: (cmn_mac.file_list) path not exist or cannot opened.;     %put %sysfunc(sysmsg());      %put directory exists (1-yes, 0-no)?  &check_dir_exist;     %put assign fileref successful (0-yes, 1-no)?  &check_file_assign;    %end;    /*   ** return result.  trim leading delimiter off front of results.   */   %if "&result" ne "" %then %do;     %qsubstr(%nrbquote(&result),2)   %end;  %mend;  /*%put %file_list(ipath=e:\blah\);*/ /*%put %file_list(ipath=e:\sasdev);*/ /*%put %file_list(ipath=e:\sasdev\,ifiles_only=1);*/ /*%put %file_list(ipath=e:\sasdev\,ifiles_only=1,ifilter=auto);*/ 

the stop_sas macro:

/****************************************************************************** ** program:  macro.stop_sas.sas **              ** description: simple macro unconditionally end running sas code **  ** parameters: nonee ** ******************************************************************************* ** version: ** 1.0 on: 25-sep-15 by: rp **     created ** 1.1 on: 05-oct-15 by: rp **     fixed typo avoid warning message ******************************************************************************/  %macro stop_sas;   %if "&sysenv" eq "fore" %then %do;     %abort cancel;   %end;   %else %do;     endsas;   %end; %mend;