Looping Steps in Enterprise Guide

[TarquinKrikery]

 Hi All,

I regularly use EG to produce (semi) automated data extractions.I have a question which i can't seem to find the answer to anywhere so hoping some of you can help.

I have an EGP which extracts a bunch of data for a bunch of a list of individuals (changes each week, as does the volume of people) from different tables from all over our database. Most of the tables are Oracle and some are HUGE and because it can takes a while to bring back the data back, I pull all the data for all individuals, at the same time.

Because of skills in house, we tend not to use a lot of Program nodes as we have to QA all work to check it's ok and there is a lack of SAS skills in department meaning this is not an issues, so I'm using Query/Append/Transpose nodes etc to get work done. 

The second 'part' of the eg, then munges the data together for the first individual in the list and formats it into a usable report which is output.

What I'm trying to do, is get the EG to then loop back to the start of the second 'part' to re-run steps 10-20 (for example) and munge together individuals 2's data, then loop back to do individual 3 etc until it's finished.

Anyone got any ideas how I can achieve this? I'm at a bit of a loss!

Cheers

[Kurt_Bremser]

My preferred course of action:

Save those steps 10-20 (or whatever) to a SAS program, parameterize via macro variables set from environment variables, and then use a shell script on the server that iterates through all your users. If you push the individual SAS batch runs into the background, you can even execute them in parallel.

Simplified example:

SAS program

%let name=%sysget(NAME);

proc print data=sashelp.class;
where name = "&name";
run;

shell script:

for NAME in Alfred Jane
do
  export NAME
  /sasconf/Lev1/SASApp/BatchServer/sasbatch.sh program.sas -print $HOME/lst/program_${NAME}.lst -log $HOME/log/program_${NAME}.log
done

[TarquinKrikery]

Thanks, I hadn't considered that. I'll have a look into this and see how I get on!

[Kurt_Bremser]

Note that once you start writing your codes like that, it becomes rather easy to have them run from a scheduling system. Commandline jobs lend themselves very well to this.

[TomKari]

Note that the above discussion involves getting into code nodes, and possibly stand-alone SAS programs. If you're comfortable with this, it's clearly the way to go.

If you want to stay within the EG tasks, I think the easiest option is to try to rearrange things to do each step for all persons at once, and then for your reporting use some kind of BY processing to individualize the reports.

Tom