topic Re: Adding a row in a table when SAS DI job starts in SAS Data Management

Adding a row in a table when SAS DI job starts

strsljen — Mon, 15 Oct 2018 09:09:00 GMT

Hi,

I need to add a row in existing Oracle table when SAS DI job starts (job name, timedate started, user who ran it...)

What would be a proper transformation to do so? Most of them expect input table and I really need only simple INSERT INTO .... statement (would like to avoid user written code if possible).

Thanks in advance!

Best regards,

Re: Adding a row in a table when SAS DI job starts

Kurt_Bremser — Mon, 15 Oct 2018 12:31:48 GMT

Create a one-row table with your data from the existing oracle table:

data append_table;
if 0 then set oracle_table; /* this gets us all the variable attributes */
name = "&jobname";
/* and so on */
run;

Then use this table in an append transformation on the Oracle table.

Re: Adding a row in a table when SAS DI job starts

LinusH — Mon, 15 Oct 2018 13:10:26 GMT

Since this is merely logging meta data I wouldn't necessarily create a transformation for it, since it doesn't tamper with the actual ETL data. So you could create a macro (assume you need this logging in several/all jobs), and call it from jobs pre-code, or even from SASBatchServer autoexec if you do it really generic...

Re: Adding a row in a table when SAS DI job starts

AngusLooney — Mon, 15 Oct 2018 15:35:46 GMT

Spooky!

Literally just wrote a macro for this, to go in the pre and post code of jobs (and nodes) to record "JobEvents" and log

things like user, job name, flow name, pid, lsfJobID, LSFFlowID, timesmap, username, compute hostname, metadata hostname etc.

The idea is to track job performance "mid-run" and also leverage things like the FlowID to allow multiple instances of the same flow to run happily synchronously, and to have a unique ID that is inherent across all jobs in the flow.

Here's the table properties:

Invocation is

%jobEvent(Event=Start) for example in the precode of the job, and %jobEvent(Event=End) in the post code,

and perhaps %jobEvent(Event=Start_Bulk_Load) and %jobEvent(Event=End_Bulk_Load) in the pre and post code of a heavy ligting table loader, for example.

The idea is the Event field is structured/categorical, and the Narrative field allows for semi-structured content, maybe like loop iteration counts, row counts, file sizes and names etc.

Re: Adding a row in a table when SAS DI job starts

VenuKadari — Mon, 15 Oct 2018 15:02:03 GMT

You could use Status Handling feature in SAS DI Studio.

Job -> Properties -> Status Handling Tab

New -> Select a condition (Perhaps "Send Job Status")

For Action, select Custom

For the Value of Action Options, add an insert statement with the macro variables you want in the output

For example:

proc sql; insert into MYLIB.MyStatus_handlingTable set message="&SYSUSERID &SYSJOBID", datetime="&SYSDATE &SYSTIME"; quit;

Re: Adding a row in a table when SAS DI job starts

AngusLooney — Mon, 15 Oct 2018 15:06:07 GMT

Nice.

Mine is in effect slightly over engineered compared to that, but it does quite a bit more, and allows for mid-job events as well.

It grew out of a need for access to the LSF attributes of a job, the JobID and the FlowID of the instance of the flow the instance of the job is in, etc.

Re: Adding a row in a table when SAS DI job starts

AngusLooney — Mon, 15 Oct 2018 15:08:21 GMT

Have to agree with you on User Written code (see my DI Worst Practice in this Forum a few months ago), but a general purpose macro you can reuse is another matter.

Re: Adding a row in a table when SAS DI job starts

strsljen — Mon, 15 Oct 2018 19:37:32 GMT

@AngusLooney Thanks for this presentation. Now I am sure I am not the only one.

If we ever meet f2f, beer is on me! 🙂

Trying to stop certain "influencers" in my company to convince the others that SAS DI is completely OK with bunch of User Written Code.

Even some SAS experts in the field (tbh, old school SAS coders tend to disrespect "modern graphical tools of the trade") think it's no big deal.

But it is. It would give us nightmare in maintenance later and I am fighting very hard against it.

As for my original question, macro function might be a nice and clean solution. Good advice! Unfortunately, I am far from expert in writing SAS macro functions. Will try to get help or it will really take time. 🙂

Best regards,

Re: Adding a row in a table when SAS DI job starts

Patrick — Tue, 16 Oct 2018 07:15:14 GMT

@strsljen

If you're using a macro then I'd implement this macro as a custom transformation.

I personally "hate" pre- and post-code as it doesn't show in the job flow. So I rather have a custom transformation which I just place into my jobs wherever I need it.

I personally tend to implement by having the macro in an Autocall library and only have the macro call in the custom transformation as this allows me to eventually change macro code in a single place without the need to redeploy all the jobs using the custom transformation.

Re: Adding a row in a table when SAS DI job starts

AngusLooney — Tue, 16 Oct 2018 09:46:53 GMT

Definitely a fan of autocall macros, though there are pros and cons that are worth discussing at some point, wrt DI jobs etc.

One thing I really like about them is that you get the best of both worlds, you can use them in code AND in User Transforms, simultaneously without loss of control. I would suggest that best practice (or at least very good practice) is to treat UT as merely the design-time UI for calls to autocall macros.

Taking this approach, there's no reason not to use an explicit UT to host the calls to the likes of jobEvent, rather than putting the call into pre/post code route.

As you say, that avoids functionality being "hidden", and essentially, would be no less "efficient" (don't get me started on the over-obsession with "efficiency"), and you are sticking to the "it's obvious" principle,