An error occurred executing the workspace job - SAS EG 7.1 (32 bit)

[dennis_oz]

Hi All ,

I am trying to automate the reading of some excel files from a folder location

I have noticed that for example , If there are 3 files when the final 3 file is read I get the error.

Similarly if 4 files are there , after reading the 4 file this error shows . 

Basically , as soon as the final file is read  I get the error . even the debugging code after that isn't executed .

%put 'loop   4-'

But all the logic before that is executed .  I tried putting  options noerrorabend; but still no luck

code 

/* Import Files */
%Macro Import_StrategicFile(rtcf_file,file_upload_date,actual_file_name,output_file);
/*let flag=1;%if &flag="1" %then*/
%if %sysfunc(exist(My_lib.&output_file.)) %then 
    %do;
	proc sql;
		select max(Response_File_Load_Date) as max_date format=date9. 
			into :max_date
				from My_lib.&output_file.;
	quit;
	 
	%put 'loop   2';

	%if %sysevalf("&max_date."d >= "&file_upload_date"d) %then
		%do;
			/*	%if &max_File_Load_Date. >= &file_upload_date. %then*/
			
			data _null_;
				rc=dosubl('%data_present_message(&output_file.,&max_date.,&file_upload_date.)');
			run;
             
			%let can = cancel;
			data _null_;
				rc=dosubl('%folder_empty_message()');
			run &can;

			%let can =;
		%end;
     %else %do;
            %put 'loop   4aaaaaaa-'  &path1\&rtcf_file. ;
            %let path1  = \\file_path\
/*			%let reporttab = Records;*/

				proc import
				datafile="&path1\&rtcf_file." out=work.&output_file._a&file_upload_date. dbms=csv replace;
				delimiter=',';
				guessingrows=32767;
				run;
/**/
                data My_lib.&output_file._&file_upload_date.;
				Format Response_File_Load_Date date9.;
/*				set &output_file. ;*/
				set work.&output_file._a&file_upload_date.;
                Response_File_Load_Date="&file_upload_date."d;
				run;

					%put 'loop   4-'  &path1\&rtcf_file. ;
			%sysexec  move "&path1\&rtcf_file." "&path1\Archive";
			 
			%put 'loop   5';
         
%end;

		%end; /*2*/
	%else 
		%do;
			%let path1 = \\file_path\;
			%let reporttab = Records;

			proc import
				datafile="&path1\&rtcf_file." out=work.&output_file. dbms=csv replace;
				delimiter=',';
				guessingrows=32767;
				run;

			data MY_LIB.%str(&output_file.);
				Format Response_File_Load_Date date9.;
				set work.&output_file.;
				Response_File_Load_Date="&file_upload_date."d;
			run;

			%sysexec  move "&path1\&rtcf_file." "&path1\Archive";
			%put 'loop   3';
		%end;
%Mend Import_StrategicFile;

[Kurt_Bremser]

I see a missing semicolon here:

            %let path1  = \\file_path\
/*			%let reporttab = Records;*/

				proc import

It might be that one of the macros you call has an ABORT or %ABORT statement that causes the server to disconnect.

Start your debugging work by properly formatting the code first; as it is, it is nigh unreadable with regards to functional blocks, as the indentation is used in a very arbitrary way, but not along the logic.

Then remove (comment) the "action" parts and replace them with %put statements, so you can see if your conditions work.

Also see Maxim 28. Using formatted macro variables forces you to use this:

%if %sysevalf("&max_date."d >= "&file_upload_date"d) %then

instead of this

%if &max_File_Load_Date. >= &file_upload_date. %then

[dennis_oz]

SYMBOLGEN:  Macro variable OUTPUT_FILE resolves to STRATEGIC_EXPORT
MPRINT(IMPORT_STRATEGICFILE):   set My_lib.STRATEGIC_EXPORT work.STRATEGIC_EXPORT;
MPRINT(IMPORT_STRATEGICFILE):   run;
WARNING: Multiple lengths were specified for the variable FIRST_NAME by input data set(s). This may cause truncation of data.
WARNING: Multiple lengths were specified for the variable PAYMENT_TYPE by input data set(s). This may cause truncation of data.
NOTE: There were 495 observations read from the data set MY_LIB.STRATEGIC_EXPORT.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-05-27T17:16:35,655+10:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| | _DISARM| 32948224| _DISARM| 10| _DISARM| 10| _DISARM| 6623417| _DISARM| 35538260| _DISARM| 0.015625| 
      _DISARM| 0.016000| _DISARM| 1906182995.640000| _DISARM| 1906182995.656000| _DISARM| 0.015625| _DISARM| | _ENDDISARM 
NOTE: There were 719 observations read from the data set WORK.STRATEGIC_EXPORT.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-05-27T17:16:35,655+10:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| | _DISARM| 31760384| _DISARM| 10| _DISARM| 10| _DISARM| 5967692| _DISARM| 35538672| _DISARM| 0.000000| 
      _DISARM| 0.000000| _DISARM| 1906182995.656000| _DISARM| 1906182995.656000| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: The data set MY_LIB.STRATEGIC_EXPORT has 1214 observations and 84 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-05-27T17:16:35,655+10:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| | _DISARM| 31760384| _DISARM| 10| _DISARM| 10| _DISARM| 6820567| _DISARM| 36394127| _DISARM| 0.000000| 
      _DISARM| 0.000000| _DISARM| 1906182995.656000| _DISARM| 1906182995.656000| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-05-27T17:16:35,655+10:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 37404672| _DISARM| 31760384| _DISARM| 10| _DISARM| 10| _DISARM| 8135651| _DISARM| 36394394| _DISARM| 0.015625| 
      _DISARM| 0.016000| _DISARM| 1906182995.640000| _DISARM| 1906182995.656000| _DISARM| 0.015625| _DISARM| | _ENDDISARM 
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              12664.21k
      OS Memory           36528.00k

Hi sorry about that . I was trying the mask the folder location .  Like I said the code just stops executing the above step without executing further more . I have indented the code .Sorry about not doing that last time.

• It  entered the cancellation code and exited the cancellation code without any abrupt ending . So that part looks fine to me 

• while reading the last file it just does not go past  the below put statements  but throws the "error occurred executing the workspace job .

                                 %put 'left point 3';

                                  %put 'entered point 4' ;

/* Import Files */
%Macro Import_StrategicFile(rtcf_file,file_upload_date,actual_file_name,output_file);
/*let flag=1;%if &flag="1" %then*/
%if %sysfunc(exist(My_lib.&output_file.)) %then 
    %do;
	      %put 'entered point  2';
	         proc sql;
	         	select max(Response_File_Load_Date) as max_date format=date9. 
		     	into :max_date
		 		from My_lib.&output_file.;
	         quit;
	 
			%put 'left point  2';

			%if %sysevalf("&max_date."d >= "&file_upload_date"d) %then
	        	%do;
		 			    %put 'entered cancellation macro';
		            	data _null_;
			        	rc=dosubl('%data_present_message(&output_file.,&max_date.,&file_upload_date.)');
		            	run;
             
		                 	%let can = cancel;
		                	data _null_;
			             	rc=dosubl('%folder_empty_message()');
			                run &can;

		                	%let can =;
							%put 'exited cancellation macro';
		          %end;

                    %else %do;
                            %put 'entered point 3';
                             %let path1 =\\FTP\In;
 
                    			proc import
			                 	datafile="&path1\&rtcf_file." out=work.&output_file. dbms=csv replace;
			                 	delimiter=',';
			                 	guessingrows=32767;
			                	run;
 
                                data  &output_file.;
			           			Format Response_File_Load_Date date9.;
								set &output_file. ;
			  					Response_File_Load_Date="&file_upload_date."d;
								run;
 
								data My_lib.&output_file.;
								set My_lib.&output_file. work.&output_file.;
								run;
           						 %put 'left point 3';
								%put 'entered point 4'    ;

								%sysexec  move "&path1\&rtcf_file." "&path1\Archive";
			 
									%put 'left point 4';
         
							%end;

		  %end;  
	%else 
		%do;
			 	%let path1 =\\FTP\In;
				%let reporttab = Records;

			proc import
				datafile="&path1\&rtcf_file." out=work.&output_file. dbms=csv replace;
				delimiter=',';
				guessingrows=32767;
				run;

			data MY_LIB.%str(&output_file.);
				Format Response_File_Load_Date date9.;
				set work.&output_file.;
				Response_File_Load_Date="&file_upload_date."d;
			run;

			%sysexec  move "&path1\&rtcf_file." "&path1\Archive";
			%put 'loop   3';
		%end;
%Mend Import_StrategicFile;

[KianSin]

hope this help https://support.sas.com/kb/42/679.html

[dennis_oz]

Hi all,
I found the issue.. there input file had carriage return characters within the columns and was a .csv file . This caused records to drop while reading and SAS was throwing an error.
Perhaps the error it tried to display on the log was too huge to be displayed in the log and hence the error .