Looping a job in DI Studio to take a series of snapshots

[TurnTheBacon]

Working in DI Studio, I've created a job that takes a snapshot from the end of a month using the INTNX function. The month is determined by a &monthback parameter set in the job properties. If the parameter is set to -1, the job creates a snapshot of the previous month (August); if set to -2 it creates a snapshot of July, etc.

The job is good for taking single snapshots, but I need to take snapshots of each month back to Feb 2009. The period variable (YYYYMM format) will be useful for this. However, as I've never worked with loops before, I don't know how to proceed.

How do I go about looping this job so that I end up with a data set that contains all the snapshots? I figure I need a "Control Table", but the SAS documentation hasn't helped me understand how it works yet.

Would it be correct to "count down" the &monthback parameter in the postcode of the Loop transformation, until the period variable equals Feb 2009 (200902)? Sounds right to me, but I think I  just need to understand how to handle the control table before I can try it... if I need one at all, that is. *goes back to studying & experimenting*

Thanks for your attention.

[Patrick]

It's all in the DIS docu but I agree that the instructions are kind of confusing. Not sure why.

May be the authors are packing too much details and variations into the beginning of the docu of this transformation instead of providing first the general idea and simple step by step instructions for the most common case and only then expanding on all the possible variations and options.

The general idea of seeting up such a loop is:

- You have a control table (called 'checklib'' in the doc). The loop transformation then executes the inner job once per row in this control table

- The control table contains the values which you want to pass to the inner job as parameters (via prompts - so the inner job gets the values in macro variables).

- To be able to pass the values from the control table to the inner job, the inner job must be parameterized (the prompts).

- In the Loop node you then link the needed variable from the control table to the parameter in the inner job

Here the main steps:

1. Control table

2. Parameterize job

3. Link inner job parameter to control table variable (in Loop node)

  The parameter name/macro variable is what you define in your inner job as prompt, the "mapped source column" is coming from your control table

[TurnTheBacon]

Thank you very much for the help. I've made progress towards understanding iterative jobs, but now I'm faced with a more concrete problem.

I have a job that successfully takes a snapshot of the end of the previous month. When its parameter ("back") is set to -1, it takes a snapshot for August. If set to -2, it takes a snapshot of July, etc. This works perfectly, and I need to iterate the job in order to build a history for a full year. So, I figure that my Control Table should contain a column named "months_back" with 12 rows, listing the values -1 to -13.

After setting up the control table with two test values (-1 and -2) in the "months_back" column, I link the Control Table to the Loop Transformation and map the parameter "back" to the source column "months_back". I then connect the Loop End, and run the job. However, the job fails and generates numerous errors saying that "a lock is not available" for the .DATA and .INDEX of the inner job's target table, and "Lock held by process 13569". It also warns that "library sashelp cannot be assigned", even though "sashelp" doesn't appear in my code.

[Patrick]

"a lock is not available" for the .DATA and .INDEX of the inner job's target table"

You get such an error if more than one process at a time wants to access a table with read access   I should have said: At least one job is locking the table using write access and other jobs want to access the same table with either read or write access.

This can happen if someone has a  table opened in SAS EG and you run a job in DIS trying to write to this table. It also could happen if your loop starts the inner job with "parallel processing" meaning that several iterations of the inner job run in parallel and the inner job writes to the same table. Make sure that this is not the case so for example make sure that your monthly extracts are written to a table with using your parameter as name, eg. "data Out&MonthBack". Not sure about the "SASHELP" warning - but don't care about it for now. Solve first the table locking issue.

[shivas · Accepted solution]

Hi,

Patrick had already given you the solution.But for example purpose you can try this

You job looks something similar to this..or you can change according to your requirement.

Loop jobs can be underwritten code or any job (in this example I have created user written code)

%let newdate=&date;

data test_&date;

set sashelp.buy;

  chardate=put(&newdate,date9.);

   sasdate=input(chardate,date9.);

   if date=intnx('month',sasdate,-1) then output;

   format sasdate date date9.;

run;

Buy table would be your parameter file with all the dates you want to pass.

&date is the prompt value(defined prompt values in job properties in one file and imported into other)

Thanks,

Shiva.