topic Re: Recove from error in SAS Programming

Recove from error

Edoedoedo — Thu, 27 Dec 2018 13:14:37 GMT

I have a sas code where I have a list of something, and I cycle some code in a macro.

%macro Run_All;

%do i=1 %to 100;
  %Run_Single(&i);
%end;

%mend;

%Run_All;

Inside each Run_Single at some point an error may occour (for instance there are duplicate id for proc transpose, or a dataset in "set" statement does not exists, or a dataset used to set some macrovars is emply so the macrovars are undefined, etc.).

If such error occurs, that is ok INSIDE the single cycle. But when the next cycle begins it must be free of all previous errors, i.e. the cycles must be independent!

How can I achieve that? If in the i-th cycle happens something, in the i+1-th cycle I get "was not replaced because this step was stopped." so sas refuses to keep processing the code.

I tryed setting "%let syscc=0", "options obs=max replace;" at the beginning of each cycle but it still breaks from i+1-th on.

Thank you

Re: Recove from error

tomrvincent — Thu, 27 Dec 2018 13:01:32 GMT

Obvious error is %do i = 1 % %to 100;

Should be %do i = 1 %to 100;

Re: Recove from error

Edoedoedo — Thu, 27 Dec 2018 13:14:08 GMT

That was a typo, fixed thank you.

The error I am talking about is INSIDE each call of Run_Single.

Re: Recove from error

tomrvincent — Thu, 27 Dec 2018 20:55:16 GMT

Any reason you can't fix those errors within run_single?

Re: Recove from error

Edoedoedo — Thu, 27 Dec 2018 21:00:02 GMT

Unfortunately not, because the data source is external, I have no control of it, and there are many many reasons because something may go wrong. I may of course check for duplicates in all tables, check for primary keys, check for existence etc, but the code would become a mess! So it is a lot better to just let the code go wrong and continue to next iteration. But, to to that sas needs to recover to errors like it was just started.

Re: Recove from error

SASKiwi — Sun, 30 Dec 2018 05:37:36 GMT

How are you running your program? If you are running in batch mode SAS will have the SYNTAXCHECK option switched on by default. That means OBS = 0 will be set as soon as SAS hits an error and no further processing of data will occur. You may get closer to what you want by using: options NOSYNTAXCHECK;

Re: Recove from error

tomrvincent — Mon, 31 Dec 2018 13:25:50 GMT

I'd suggest do a google search for 'sas continue after error' and see if any of those solutions work for you.

Re: Recove from error

ballardw — Mon, 31 Dec 2018 23:50:55 GMT

The appropriate solution is to determine such thing as why a data set is empty prior to using it or have code using the set test before use.

You might be able to work with this %macro if you can determine why or what is persisting between cycles. You should be able to reset the macro symbol table between YOUR calls to this macro and possibly clean up afterwards. Conceptually:

%macro Run_All;

%do i=1 %to 100;
  %MyCleanBeforeMacro
  %Run_Single(&i);
   %MyCleanAfterMacro
%end;

%mend;

But we don't have enough information about what may be getting set, read or stored by a not-very-well designed program.

Your example of duplicate ID in proc transpose is data set related. So what kind of persistence is there that would cause that again? If the same data sets are used you can't expect a "clean" run if the bad data is still there.

Things like a data set existing are pretty easy to test for so beat up who ever is supplying %Run_single to fix that at least. Here's an example.

data _null_;
   if exist("work.mydata") then call symputx('itexists',1);
   else call symputx('itexists',0);
run;

%if &itexists = 1 %then %do;
<whatever is required when the set work.mydata exists>
%end;
%else %do;
<whatever makes sense when work.mydata does not exist>
%end;

OR you test for the data set in the "clean before" and possibly don't even bother to call the %Run_single since it would "fail".

Depending on how you are setting the macro variables from a data set you have different options. If using a data set then the code above is easy to modify when one or more variables that are required are not available (or missing) in the data set. If using Proc SQL with an :into the automatic macro variable &sqlobs is likely set with the number of elements in the list. If 0 then you have flag to set other variables or skip processing steps.

I have to say that I am not impressed with what appears to be production code with so many beginner errors.

You may have to run your code with OPTIONS MPRINT SYMBOLGEN and/or MLOGIC and possibly send you log to a text file as the log can get very large to find what is actually breaking.

Re: Recove from error

Edoedoedo — Wed, 09 Jan 2019 08:52:12 GMT

"no syntaxcheck" was exactly what I was looking for thank you, now the i+1-th iteration runs correctly even though the i-th got some error.

Re: Recove from error

Edoedoedo — Wed, 09 Jan 2019 08:59:29 GMT

Yes you are right, if I am aware of what can be wrong I use to insert some "checkpoints" in the code to check if a table exists, if a table is empty, etc.

But there are some cases (like this one) in which if you use external sources, external databases, external files (excel, etc), there are so many things that may go wrong that it would be very very very verbose to predict all possibilities (connection failed, schema does not exists, table does not exists, column does not exists, column type is wrong, a table is not well indexed on a databases so the query has a timeout, an excel has a wrong number of columns, an excel sheet name is wrong, etc etc sooo many).

So in these case I prefer to just run the code and check at the end if something went wrong: if so, I notify the users responsible of the sources and I proceed to the next iteration. If they want their output, they must fix their sources and then the procedure can run again, that's perfectly fine! I don't care and I move on to the next iteration which is completely independent of the results of the previous one.

Thank you again

Regards

Re: Recove from error

SASKiwi — Wed, 09 Jan 2019 19:35:02 GMT

@Edoedoedo - I like your approach. I agree it is impossible to cater for everything that might go wrong with an application. NOSYNTAXCHECK is a great option for ensuring SAS continues processing if there is a problem. For non-iterating batch jobs I prefer SYNTAXCHECK as it causes the job to complete very quickly and makes it very obvious there has been an error.