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;
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
I have entered " options mprint mlogic ; "
could that be the issue . I removed that but have not been able to test the code ..
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;
i am lost
did you mean || strip(file_date) || rather than !! strip(file_date) !!
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.
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
thanks the code is working for me as well .. I don't know why it showed a lock the first time
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.