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;
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
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.
%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;
hope this help https://support.sas.com/kb/42/679.html
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.