Search code examples
sassas-dis

Creating a user defined transformation in SAS DIS to produce work table

I have code which produces some job specific variables in SAS DIS, and rather than having to copy this code into a User written code block for every job I write, I would like to have a predefined transformation to do this which I would drag from the transformations pane into any given job.

When creating a new transformation, in the SAS Code component of the dialogue, I paste some simple code. Let's say it looks like this:

DATA CREATE_JOB_SPECIFIC_VARS;
    job_name = "&etls_jobName";
    job_date = date();
RUN;

I don't need any prompts, so I select "Next" on the Options component of the dialogue, and I don't need any inputs so I set the minimum and maximum number of inputs to 0. I need 1 output (the work table I create with the code) so I leave the associated values as they are. I select "Next" and "Finish".

When I try to bring the transformation into a job and run it, two unexpected things occur:

  1. The output table of the transformation cannot be viewed, and attempting to do so yields the error:

Error accessing metadata: Table has no columns 2. Running the transformation causes another transformation in the job to be connected to the input end of the newly created transformation (which should not accept any inputs).

I am mainly concerned with issue #1 (#2 is a curiosity which is of secondary importance). I'm guessing the issue arises since the software wants the columns of the output table to be specified somewhere other than the code block, but I see nowhere else that this can be done.

Is what I want to do possible? Must I create the job specific variables as macro variables (it is desirable to avoid this)?

Much gratitude!

Solution

  • For any custom written code dumping result in output table of the transformation would ideally show this error if the metadata of the output table is not defined. Just define the metadata by right clicking the table -> Properties -> Column tab and then you would stop getting the 1st error. Also, I'm assuming that you have altered the physical name of the output table (right clicking the output table -> Properties -> Physical Storage tab ) to CREATE_JOB_SPECIFIC_VARS since you are using "DATA CREATE_JOB_SPECIFIC_VARS;".

    For the 2nd issue, if 2 or more separate flows are created within a single job flow then when the job flow is executed for the 1st time a dash line ( - - - ) is automatically generated which connects the separate flow in the order of control flow section. This is merely a way of showing user, the execution order of the job flow when it's executed. I think it's great that SAS DI Studio shows it otherwise it would be difficult to know from the diagram the order of the flow.

    Let me know if it helps!