topic Re: Send output of one job as input to other in SAS Data Management

Send output of one job as input to other

forumsguy — Wed, 25 Sep 2013 16:48:50 GMT

Hi all,

I am creating two DI ETL jobs.

My first job is audit job which will create process_date as macro variable which I will be passing as input to next extract job which would extract data based upon process date macro variable.

Has anyone encountered situation like this? Need guidance in passing variable as input to others

Re: Send output of one job as input to other

Reeza — Wed, 25 Sep 2013 17:44:53 GMT

If your process_date is a Global macro variable not sure there's an issue. Though I'd be careful with my naming and making sure it was a proper SAS date.

%include 'audit1.sas'; *Generates macro variable proces_date which has a scope of global.

%second_audit(run_date=&process_date);

Re: Send output of one job as input to other

LinusH — Wed, 25 Sep 2013 20:15:32 GMT

It's not common that DI jobs are tied together by a self written SAS "script" using %includes.

A design question, audit job, is the creation of a macro variable the only task? Will this be repeated to be used as input to other extract jobs?

If not, I suggest that you merge the two jobs into one.

Another DI way is try to have the audit job as an outer job, and you try to pass the macro variable as a parameter to the extract job.

Re: Send output of one job as input to other

Patrick — Thu, 26 Sep 2013 02:10:21 GMT

You could as Linus suggests have your audit job as outer job calling your second job as inner job. This way you could pass a macro variable.

I assume your "audit job" will maintain some kind of control table. If yes then the way I've seen this done is to implement this as a user transformation. You then simply use this user transformation in your jobs (passing the job name as parameter with is the key to your control table).

If this is what you intend to do: In order to avoid table locking issues with your control table make sure that you either run your jobs in sequence or that the control table is under SAS/Share or in a database where parallel read/write is possible.