topic Re: Making EG Stop Executing subsequent process flows based on a value in the previous process flow in SAS Enterprise Guide

Making EG Stop Executing subsequent process flows based on a value in the previous process flow

Arun_SP — Mon, 21 Nov 2016 09:24:33 GMT

I have a EG project which has multiple process flows in SAS EG like "Process Flow A", "Process Flow B", "Process Flow C" and so on in that order. I have a final output table called "Table1" in "Process Flow A" from a query builder task. When I run the whole project, EG will execute the process flows in sequence starting from "Process Flow A".

After EG has succesfully run the "Process Flow A" and generated the final output which is "Table1", I want EG to proceed running the subsequent process flows ("Process Flow B", "Process Flow C" and so on) only if the "Table1" is empty, if not I want it to stop running. I don't want it to end the EG session and lose all the data (WORK), I just want it to stop executing further. I am using SAS EG 7.1. How do I achieve this?

Thanks

Re: Making EG Stop Executing subsequent process flows based on a value in the previous process flow

RW9 — Mon, 21 Nov 2016 09:30:24 GMT

To be honest, I have seen this question a few times on here, and it really doesn't make sense to me. If you have planned a process thoroughly, then the end of that should be a report which either contiains output, or is just a report stating that nothing wass returned at X point. Just stopping a process at a certain point tells the end user nothing.

It can be done, just query sashelp.vtable (where=(libname="your libname" and memname="the dataset in question)); and if nobs=0 then exit, or dont' call the next program.

However, I still say that its better to run through the process - put a conditional round any blocks which use this data which might be empty, and then output a report at the end stating process run and complete and here are your outputs one of which is empty as nothing returned from program XYZ.

Re: Making EG Stop Executing subsequent process flows based on a value in the previous process flow

CaseySmith — Mon, 21 Nov 2016 22:13:17 GMT

A new feature in EG 7.13, releasing *very* soon, could provide another way to achieve this...

Prior to EG 7.13, when an error occurred in a running element in your process flow, execution would continue regardless of the error. Now you can control the behavior with the following project (or element) option:

So, following your example, you could set the "Action to take on errors during execution" to "Stop all items" and add a program at the end of "Process Flow A" that puts out an error if "Table1" is not empty. Example code:

proc sql;

select count(*) into :recordCount from table1;

quit;

%macro putErrorIfNotEmpty;

%if &recordCount > 0 %then %put ERROR: Not empty. Do not continue processing.;

%mend;

%putErrorIfNotEmpty;

If Table1 is not empty, the program that checks at the end of "Process Flow A" will result in an error, which will stop all downstream execution (your other process flows will not run).

Re: Making EG Stop Executing subsequent process flows based on a value in the previous process flow

Arun_SP — Tue, 22 Nov 2016 02:01:45 GMT

@RW9 Thank you for your response. I have very very little experience with SAS programming, so I am not sure how to acheive this and I am stuck. Here's what I did so far. I added Program at the end of the Process Flow A (Linked it to the final table: Table1) with the following macro.

%macro stop_execute;
proc sql;
select nobs into :Count_Rows from SASHELP.VTABLE WHERE LibName = 'WORK' AND MemName = 'Table1' ;
quit;
%if &Count_Rows > 0 %then "How do I ask EG to stop executing further?";
%else "How do I ask EG to continue executing further?";
%mend;
%stop_execute;

I am not able to figure out what should be written in the "How do I ask EG to stop executing further?" and "How do I ask EG to continue executing further?" parts. Please help.

Re: Making EG Stop Executing subsequent process flows based on a value in the previous process flow

RW9 — Tue, 22 Nov 2016 09:35:28 GMT

The way I would do it is to: if no obs then create template balnk table and continue:

data _null_;
  set sashelp.vtable (where=(libname="your lib" and memname="your dataset"));
  if nobs= 0 then call execute('data want; result="No observations were found"; run;');
run;

So if there are no observations in your dataset, then a template dataset with just the one observation is created, which can then go further into your process and be reported as such. No need for conditional execution.

Re: Making EG Stop Executing subsequent process flows based on a value in the previous process flow

Patrick — Tue, 22 Nov 2016 10:21:34 GMT

You can end a data step from iterating further using statement STOP, but you can't really tell SAS to no more execute downstream run groups based on some condition.

You could issue issue statements like ENDSAS or ABORT but this wouldn't only stop your current programs from running, it would end your SAS session (=disconnect your EG session from the server) and you loose all your data in Work.

The only "hack" I can currently think of would be to wrap a macro around your flows and implement conditional processing logic this way.

Per flow have a program node at the very beginning and at the very end of the flow.

Code at the beginning of the flow.

%macro wrapper(libname=, memname=);
  %local nobs;
  %let nobs=0;
  proc sql;
    select nobs into :nobs
    from dictionary.tables
    where libname=%upcase("&libname") and memname=%upcase("&memname")
    ;
  quit;

  %put &=nobs;

  %if %eval(&nobs>0) %then %do;
  /********************************/

Code at the end of the flow:

  /*******************************/
  %end;
%mend;
%wrapper(libname=WORK, memname=TABLE1)

Re: Making EG Stop Executing subsequent process flows based on a value in the previous process flow

Arun_SP — Thu, 01 Dec 2016 02:27:10 GMT

@Patrick Thank you for your response, I tried what you said, so Process Flow A has a table at then end named Work.Table1 as mentioned earlier. In Process Flow B, I added what you said,

When I run the project (which contains both Process Flow A & B). I get error "49" followed by the note

"NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended."

And this error seemed to spread to all the components in the project. I tried removing the commented out sections of the macro (/***....**/ after the %if statement and the same before %end) as suggested by andrewm in this post "NOTE 49-169: The meaning of an identifier after a quoted string....." error. But it doesn't solve the issue!

Please help.

Re: Making EG Stop Executing subsequent process flows based on a value in the previous process flow

Patrick — Sun, 04 Dec 2016 21:24:44 GMT

@Arun_SP

I can replicate what you describe. The code as such would be totally fine and if you export the code in the flow and then run everything in a single program node, things work.

It appears that EG adds at the beginning and the end of every single node some additional code before execution and this seems to mess up this wrapper macro approach. I've done some testing and this already happens when you have nothing else than a wrapping "%macro /%mend" bracket without any further code.

I couldn't find a work-around so sorry for posting a solution which doesn't work for your use case.

One could ask the question if this is an EG bug as it's EG messing up totally valid code.

Re: Making EG Stop Executing subsequent process flows based on a value in the previous process flow

constliv — Sun, 26 Nov 2017 12:12:17 GMT

Arun_SP,

This worked exactly as you wanted in SAS EG 5.1 in terms of stopping program execution (the count conditions are specific to my requirement):

	data _null_ ;
	if &cnt > 0 then DO ;
	PUT 'ERROR:There are CustomerGroup Numbers in the Plan B listing that are not themselves listed as CustomerNos also. Add these to the dataset cust_mapping_by_rank.' ;
	ABORT CANCEL;
	END;
	if &cnt = 0 then DO ;
	file print;
	PUT 'All CustomerGroup Numbers in the Plan B listing are now listed themselves listed as CustomerNos also.';
	end;
	file log;
	run ;

ABORT CANCEL is the correct statment.

The WORK library contents are retained and the SAS server is not disconnected. At the same time, no further programs from the SAS EG project are executed. Subsequent DATA / PROC segments within the same program are also NOT executed. An ERROR is written to the log.

At least, this has been my experience.