1

I have a SAS DIS job which extracts and processes some timestamped data. The nature of the job is such that the data must be processed a bit at a time, month by month. I can use a time filter to ensure any given run is within the required timeframe, but I then must manually change the parameters of that table and rerun the job, month by month, until all the data is processed.

Since the timeframes extend back quite far, I'd like to automate this process as much as possible. Ideally I'd have a table which has the following form:

time_parameter_1 time_parameter_2
2JAN2010       1FEB2010
2FEB2010       1MAR2010
...            ...

which could be part of an iterative job which continues to execute my processing job with the values of this table as time parameters until the table is exhausted.

From what I understand, the loop transformation in SAS DIS is designed to loop over tables, rather than rows of a table. Is the solution to put each date in a separate table, or is there a direct way to achieve this?

Much gratitude.

EDIT

So, with the help of Sushil's post, I have determined a solution. Firstly, it seems that SAS DIS requires the date parameters to be passed as text and then converted to the desired date format (at least, this is the only way I could get things to work).

The procedure is as follows:

In the grid view of the job to be looped over, right click and select Properties. Navigate to the Parameters tab and select New Group. Name the parameter in the General tab (let's use control_start_date) and in the Prompt Type and Values tab select Prompt type "Text". Press OK and add any other parameters using the same method (let's say control_end_date is another parameter).

Create a controlling job which will loop over the parameterized job. Import or create a table of parameters (dates) to loop over. These should be character representations of dates.

Connect the table of parameters to a Loop transformation, connect the parameterized job to the right end of the Loop transformation, and connect the right end of the parameterized job to a Loop End transformation.

Right click the Loop transformation and select Properties. Select the Parameter Mapping tab and properly map the control table date columns to the parameters of the parameterized job (control_start_date and control_end_date). In the Target Table Columns tab ensure that the parameter columns are mapped to the target table. Select OK.

In the parameterized job, create a User Written Code transformation. Create the columns start_date and end_date (type DATE9.) and populate the output work table using the following code:

DATA CONTROL_DATES;

    start_date = input(trim("&control_start_date"),DATE9.);
    end_date = input(trim("&control_end_date"),DATE9.);

RUN;

Connect the dates in the work table WORK.CONTROL_DATES to the logic of the job (possibly with a join) so that they serve as filters in the desired capacity. Save the parameterized job.

Now running the controlling job should be able to loop over the job using the specified date filters.

A lot of this is described in the following PDF, but I'm not sure how long that link will survive and some of the issues I encountered were not addressed there.

Community
  • 1
  • 1
Rookatu
  • 1,487
  • 3
  • 21
  • 50

1 Answers1

1

Your understanding about the LOOP transformation is incorrect. You do not need separate table for loop transformation to make your parameterized job flow loop. The table that has the time parameters can be the input to the loop transformation and the parameterized job can loop based on control table(input table for loop transformation).

Here is an example usage of loop transformation which is different from the one mentioned in the SAS DI Studio Documentation and is relevant to your problem : PDF

Let me know if it helps!

sushil
  • 1,576
  • 10
  • 14
  • Hey sushil. I am having trouble getting things to work following that document. I suspect that I just need some more time to digest the contents, but right now the pressure is on to achieve my goal, so I'll take a brute force approach for the moment and then revisit the document. +1 for now though, and thanks very much! – Rookatu Apr 27 '15 at 15:08
  • Hey Sushil. I've got the looping job set up. The parameters in the job to be looped are `control_start_date` and `control_end_date`, and are of type date. When I try to run the job to be lopped with test values for the prompts, I get the error message "Invalid date/time/datetime constant '&control_start_date'D". Is there another expression I should be using to reference the job parameters? Thanks! – Rookatu Apr 29 '15 at 13:41
  • It looks like you are getting the error when you are using the parameters in the parameterized job flow(which is going to get looped). if in control table variable control_start_date & control_end_date format is of date9. then usage of the parameter in the parameterized job flow would be like my_date >= "&control_start_date"D . If control table had no format for date then usage would be like my_date >= %unquote(&control_start_date) . Please note that my_date is a date column on a dummy table. – sushil Apr 29 '15 at 16:36
  • If you still have trouble try adding USER WRITTEN transformation in the parameterized job flow and put %PUT _USER_; in the transformation to see how values are resolved. – sushil Apr 29 '15 at 16:36
  • Hey sushil. Let's forget about the controlling job for a second. On just the parameterized job itself I have the parameters `control_start_date` and `control_end_date` and they are of type Date, and are given test values. In a user written code transformation I have the instruction: `start_date = '&control_start_date'D`. It is this instruction which gives the error mentioned above. Are my dates supposed to be encoded as characters? If so, do you know how it should be done? Thanks so much! – Rookatu Apr 29 '15 at 17:16
  • your macro variable should be in double quotes instead of single quotes. start_date = "&control_start_date"D – sushil Apr 29 '15 at 17:23
  • Okay, this resolves the error in the parameterized job, but the output is null (`.`), and when running the controller job the error about invalid datetime constants persists. I think I'm getting closer with your help! Any ideas? – Rookatu Apr 29 '15 at 17:36
  • Okay, i think i got it.Make sure the following 1) control table is connnected to Loop transf'n and Parameter mapping is correctly done. Make sure date in control table is of date type. 2) In the Parameterized job -> Properties -> Parameters make sure the control_start_date and control_end_date type is of DATE 3) Within parameterized job to use the parameters %unquote the macro variable so that they are resolved as number and then compare to any date columns. e.g. data dummy; x=%unquote(&control_start_date); y=%unquote(&control_end_date); format x y yymmdd10.; run; proc print;run; – sushil Apr 29 '15 at 18:25
  • When I try to use `%unquote` as you specify I get the error "Syntax error, expecting one of the following:" followed by the usual symbols. I believe that using `"&control_start_date"D` works after all: the issue was that the test values weren't working, but setting a default value works. However, in the controller job I am still getting those errors mentioned earlier. I've checked that the columns are mapped properly in the Parameter Mapping tab of the Loop transformation. – Rookatu Apr 29 '15 at 18:45
  • I think that `%unquote` must be applied to `"&control_start_date"D` rather than `&control_start_date` (at least the former does not give errors), but the controller job still doesn't like this, yielding the error " Invalid date/time/datetime constant "19725"D" when I run the controller job. – Rookatu Apr 29 '15 at 20:27
  • can you update the link as it seems to no longer work. Thank you – kristof Nov 24 '15 at 12:50