Lock Error on SAS Dataset Library when executing multiple PROC import simultaneously

[dennis_oz]

Hi , can someone please help me with this error. Thanks,

Error I am getting:

ERROR: Entry lock is not available for IMPORT_STRATEGICFILE.MACRO in catalog WORK.SASMAC1, lock held by IOM ROOT COMP ENV.

ERROR: The macro IMPORT_STRATEGICFILE could not be saved in the WORK.SASMACR catalog.  A previous version of the macro was in use

       and could not be replaced.

ERROR: The SAS Macro Facility has encountered an I/O error.  Canceling submitted statements.

Code that I am executing

%Macro Import_StrategicFile(rtcf_file,output_file);

%let path1 = xxxx;
%let reporttab = Records;

proc import
datafile="&path1\&rtcf_file." out=My_lib.&output_file. dbms=xlsx replace;
/* datafile="&path1\&rtcf_file." out=work.&output_file. dbms=xlsx replace;*/
getnames=yes;
sheet="&reporttab.";
run;
/* to move the fimported file to archive location*/
%sysexec move "&path1\&rtcf_file." "&path1\Archive";

%Mend Import_StrategicFile;


data _null_;
set wash_file;
call symput ('file_date',file_date);
select (actual_file_name);
when ('AAAA') rc=dosubl('%Import_StrategicFile(AAAA_&file_date.,HI_AAAA)'); /* doing pro import to same library*/
when ('BBBB') rc=dosubl('%Import_StrategicFile(BBBB_&file_date.,BBBB)'); /* doing pro import to same library*/
when ('CCCC') rc=dosubl('%Import_StrategicFile(CCCC_&file_date.,CCCC)'); /* doing pro import to same library*/
otherwise put=hi;
end;
run;

[dennis_oz]

I have entered  " options mprint mlogic ; " 

could that be the issue . I removed that but have not  been able to test the code .. 

[Kurt_Bremser]

Your code as such won't work:

data _null_;
set wash_file;
call symput ('file_date',file_date); /* this is executed at data step _runtime_ */
select (actual_file_name);
when ('AAAA') rc=dosubl('%Import_StrategicFile(AAAA_&file_date.,HI_AAAA)'); /* doing pro import to same library*/
/* the reference to &file_date will not be resolved because of the single quotes */
/* but it won't work anyway, as it would be resolved at data step _compile time _*/
when ('BBBB') rc=dosubl('%Import_StrategicFile(BBBB_&file_date.,BBBB)'); /* doing pro import to same library*/
when ('CCCC') rc=dosubl('%Import_StrategicFile(CCCC_&file_date.,CCCC)'); /* doing pro import to same library*/
otherwise put=hi; /* You try to assign the value of variable hi to variable put; is this what you intended? */
end;
run;

I suggest these changes:

data _null_;
set wash_file;
select (actual_file_name);
  when ('AAAA') rc=dosubl('%Import_StrategicFile(AAAA_' !! strip(file_date) !! ',HI_AAAA)'); /* doing pro import to same library*/
  when ('BBBB') rc=dosubl('%Import_StrategicFile(BBBB_' !! strip(file_date) !! ',BBBB)'); /* doing pro import to same library*/
  when ('CCCC') rc=dosubl('%Import_StrategicFile(CCCC_' !! strip(file_date) !! ',CCCC)'); /* doing pro import to same library*/
  otherwise put=hi; /* change as needed */
end;
run;

[dennis_oz]

i am lost 

did you mean || strip(file_date) || rather than  !! strip(file_date) !!

[Kurt_Bremser]

The double exclamation signs are syntactically equivalent to the double pipe character.

But as @yabwon demonstrated, the other method will also work, but I prefer to use macro elements only when absolutely needed.

[yabwon · Accepted solution]

Hi, 

I did small experiment. I've created 3 xlsx files `AAAA_X.xlsx`, `BBBB_X.xlsx`, and `CCCC_X.xlsx` in my WORK location, I also created `Archive` subfolder and I used following dataset and MyLib:

libname My_lib (work);

data wash_file;
  actual_file_name = 'AAAA'; file_date= "X.xlsx"; output;
  actual_file_name = 'BBBB'; file_date= "X.xlsx"; output;
  actual_file_name = 'CCCC'; file_date= "X.xlsx"; output;
run;

than only change i did with you code was 

%let path1 = %sysfunc(pathname(work));
%let reporttab = Arkusz1; 

to adjust my excels and their names

after running:

resetline;
libname My_lib (work);

data wash_file;
  actual_file_name = 'AAAA'; file_date= "X.xlsx"; output;
  actual_file_name = 'BBBB'; file_date= "X.xlsx"; output;
  actual_file_name = 'CCCC'; file_date= "X.xlsx"; output;
run;

%Macro Import_StrategicFile(rtcf_file,output_file);

%let path1 = %sysfunc(pathname(work));
%let reporttab = Arkusz1;  

proc import
datafile="&path1\&rtcf_file." out=My_lib.&output_file. dbms=xlsx replace;
/* datafile="&path1\&rtcf_file." out=work.&output_file. dbms=xlsx replace;*/
getnames=yes;
sheet="&reporttab.";
run;
/* to move the fimported file to archive location*/
%sysexec move "&path1\&rtcf_file." "&path1\Archive";


%Mend Import_StrategicFile;

data _null_;
set wash_file;
call symput ('file_date',file_date);
select (actual_file_name);
when ('AAAA') rc=dosubl('%Import_StrategicFile(AAAA_&file_date.,HI_AAAA)'); /* doing pro import to same library*/
when ('BBBB') rc=dosubl('%Import_StrategicFile(BBBB_&file_date.,BBBB)'); /* doing pro import to same library*/
when ('CCCC') rc=dosubl('%Import_StrategicFile(CCCC_&file_date.,CCCC)'); /* doing pro import to same library*/
otherwise put=hi;
end;
run;

I have nice and clear log without errors:

1    libname My_lib (work);
NOTE: Libref MY_LIB was successfully assigned as follows:
      Levels:           1
      Engine(1):        V9
      Physical Name(1): R:\_TD32_YABWON700_
2
3    data wash_file;
4      actual_file_name = 'AAAA'; file_date= "X.xlsx"; output;
5      actual_file_name = 'BBBB'; file_date= "X.xlsx"; output;
6      actual_file_name = 'CCCC'; file_date= "X.xlsx"; output;
7    run;

NOTE: The data set WORK.WASH_FILE has 3 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


8
9
10
11
12   %Macro Import_StrategicFile(rtcf_file,output_file);
13
14   %let path1 = %sysfunc(pathname(work));
15   %let reporttab = Arkusz1;
16
17   proc import
18   datafile="&path1\&rtcf_file." out=My_lib.&output_file. dbms=xlsx replace;
19   /* datafile="&path1\&rtcf_file." out=work.&output_file. dbms=xlsx replace;*/
20   getnames=yes;
21   sheet="&reporttab.";
22   run;
23   /* to move the fimported file to archive location*/
24   %sysexec move "&path1\&rtcf_file." "&path1\Archive";
25
26
27   %Mend Import_StrategicFile;
28
29   data _null_;
30   set wash_file;
31   call symput ('file_date',file_date);
32   select (actual_file_name);
33   when ('AAAA') rc=dosubl('%Import_StrategicFile(AAAA_&file_date.,HI_AAAA)'); /* doing pro import to same library*/
34   when ('BBBB') rc=dosubl('%Import_StrategicFile(BBBB_&file_date.,BBBB)'); /* doing pro import to same library*/
35   when ('CCCC') rc=dosubl('%Import_StrategicFile(CCCC_&file_date.,CCCC)'); /* doing pro import to same library*/
36   otherwise put=hi;
37   end;
38   run;

NOTE: Variable hi is uninitialized.
NOTE: The import data set has 2 observations and 3 variables.
NOTE: MY_LIB.HI_AAAA data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


NOTE: The import data set has 2 observations and 3 variables.
NOTE: MY_LIB.BBBB data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: The import data set has 2 observations and 3 variables.
NOTE: MY_LIB.CCCC data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: There were 3 observations read from the data set WORK.WASH_FILE.
NOTE: DATA statement used (Total process time):
      real time           4.80 seconds
      cpu time            0.89 seconds

1) are there any other assumptions about your data we don't know?

2) your datastep has issues: `NOTE: Variable hi is uninitialized` 

3) are your input data ok?

Bart

[dennis_oz]

thanks the code is working for me as well .. I don't know why it showed a lock  the first time