Date: Thu, 19 Jan 2006 10:11:45 -0500
Reply-To: "Rickards, Clinton (GE Consumer Finance)"
<clinton.rickards@GE.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Rickards, Clinton (GE Consumer Finance)"
<clinton.rickards@GE.COM>
Subject: Re: SAS ETL studio - How to call ETL jobs from base/macro SAS
Content-Type: text/plain; charset="iso-8859-1"
Ankit,
It's unclear to me what you want to do with the existing macros and the calling them from ETL Studio. But a few things come to mind.
1. ETL Studio jobs which are to be run in production must be deployed. Deployment directories are defined in Management Console and associated with a SAS Batch Server. When a job is deployed, ETL Studio generates a static SAS program in the deployment directory. Until the job is deployed the job only exists in metadata.
2. Macros can be called by User Written Code transformations that you place in the job, in the pre- and post-process steps for the job, and by writing your own code for a SAS-supplied transformation (e.g. Extract, Join, Sort). My suggestion would be to control the fmtsearch option so that the macros can be autocalled or you can %include the macro files. This allows you to modify the macro without having to redeploy the job.
3. Look at the status handling for a transformation or the job to maintain the history you are talking about. You can use one of the pre-built actions or use Custom, which can call a macro. Remember that you can have several checks in status handling and thus handle multiple situations. Since some of these capabilities are pretty basic, see #4.
4. You can write your own SAS code transformation to record the update information you want and place it immediately after a data set in the job. I've used SAS Code Transformations quite often to good effect, both to handle job-specific issues (e.g. multiple inputs and multiple outputs in a single transformation) and for shared capabilities (e.g. emails, conditional execution, duplicate processing in multiple jobs).
I hope this helps...
Clint
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of
Ankit
Sent: Thursday, January 19, 2006 1:47 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SAS ETL studio - How to call ETL jobs from base/macro SAS
Hi,
I am trying to develop a datamart on SAS ETL studio which I had
otherwise developed using BASE SAS & Macro SAS. However, I am facing
some problem in developing the datamart through ETL studio. I have one
main macro that reads in the process (basically macro) names that are
to be executed and then executes them one after another. The process
information is logged in control tables (also on SAS). The information
logged is related to start/end date-time of the process, the return
code etc.
I am not quite sure how I can use ETL studio for achieving the same
objective. I can create the processes (macros) as jobs on ETL studio.
However the main macro will be on Base/Macro SAS. Now, how can I call
the ETL jobs from this main program which is on Base/Macro SAS? I
assume we cannot have a main program created on ETL studio. The reason
I am asking this question is because I could not see where the ETL jobs
get stored physically (as a .sas file)
Can anybody help me in solving this problem.Is a extensive use of User
Tranformation templates a common practice?
Also, I had a discussion with my friends who are working on
Informatica. According to them Informatica provides some inbuilt
metadata tables that stores information related to which table was
updated by which job and at what date/time and user etc..is there
anything like that available on SAS ETL studio also?
Thanks,
Ankit
