11-21-2016 04:24 AM
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?
11-21-2016 04:30 AM
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.
11-21-2016 09:01 PM
@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.
11-22-2016 04:35 AM
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.
11-22-2016 05:21 AM
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)
11-30-2016 09:14 PM - edited 11-30-2016 09:27 PM
@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!
12-04-2016 04:24 PM
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.
11-21-2016 05:13 PM
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:
select count(*) into :recordCount from table1;
%if &recordCount > 0 %then %put ERROR: Not empty. Do not continue processing.;
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).
11-26-2017 07:04 AM - edited 11-26-2017 07:12 AM
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.