BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TurnTheBacon
Fluorite | Level 6

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. Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
shivas
Pyrite | Level 9

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.

report.png

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.

View solution in original post

4 REPLIES 4
Patrick
Opal | Level 21

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

Capture.PNG

2. Parameterize job

Capture.PNG

Capture.PNG

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

Capture.PNG

  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
Fluorite | Level 6

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
Opal | Level 21

"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
Pyrite | Level 9

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.

report.png

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 4726 views
  • 9 likes
  • 3 in conversation