如何使 SAS DIS 作业循环遍历参数表的行 [英] How to make a SAS DIS job loop over rows of a parameter table

问题描述

我有一个 SAS DIS 作业，它提取和处理一些带时间戳的数据.这项工作的性质是，必须逐月一次处理一点数据.我可以使用时间过滤器来确保任何给定的运行都在要求的时间范围内，但我必须手动更改该表的参数并逐月重新运行作业，直到处理完所有数据.

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.

据我了解，SAS DIS 中的循环转换旨在循环遍历表，而不是表的行.是将每个日期放在单独的表格中的解决方案，还是有直接的方法来实现这一点?

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?

非常感谢.

编辑

所以，在 Sushil 的帖子的帮助下，我确定了一个解决方案.首先，SAS DIS 似乎需要将日期参数作为文本传递，然后转换为所需的日期格式(至少，这是我可以让事情正常工作的唯一方法).

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).

程序如下:

在要循环的作业的网格视图中，右键单击并选择属性.导航到参数选项卡并选择新建组.在常规选项卡中命名参数(让我们使用 control_start_date)并在提示类型和值选项卡中选择提示类型文本".按 OK 并使用相同的方法添加任何其他参数(假设 control_end_date 是另一个参数).

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.

将参数表连接到 Loop 转换，将参数化作业连接到 Loop 转换的右端，并将参数化作业的右端连接到 Loop End 转换.

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.

右键单击循环转换并选择属性.选择参数映射选项卡并将控制表日期列正确映射到参数化作业的参数(control_start_date 和 control_end_date).在 Target Table Columns 选项卡中，确保参数列映射到目标表.选择确定.

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.

在参数化作业中，创建用户编写的代码转换.创建列 start_date 和 end_date(键入 DATE9.)并使用以下代码填充输出工作表:

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;

将工作表 WORK.CONTROL_DATES 中的日期连接到作业的逻辑(可能带有连接)，以便它们充当所需容量的过滤器.保存参数化作业.

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.

以下 PDF，但我不确定该链接会存在多长时间，而且我遇到的一些问题也没有得到解决.

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.

推荐答案

你对LOOP转换的理解有误.您不需要单独的表格进行循环转换来使您的参数化作业流程循环.具有时间参数的表可以作为循环变换的输入，参数化的作业可以基于控制表(循环变换的输入表)循环.

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).

这是循环转换的示例用法，它与 SAS DI Studio 文档中提到的不同，并且与您的问题相关:PDF

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

如果有帮助请告诉我！

这篇关于如何使 SAS DIS 作业循环遍历参数表的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！