f



Re: SAS ETL studio - How to call ETL jobs from base/macro SAS

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
0
Rickards
1/19/2006 3:11:45 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
969 Views

Similar Articles

[PageSpeed] 29

Reply:

Similar Artilces:

Need SAS Data Integration Consultant with SAS Data Integration Studio, Base SAS, SAS macros and SQL experience for a 9 months project at Charlotte NC, please send resumes to kamesh@kmmtechnologies.com
Need SAS Data Integration Consultant with SAS Data Integration Studio, Base SAS, SAS macros and SQL experience for a 9 months project at Charlotte NC, please send resumes to kamesh@kmmtechnologies.com ...

Re: What r the data types in SAS? in Base SAS , and SAS SQL
> From: Amar Mundankar > Sent: Tuesday, July 21, 2009 8:10 AM > To: sas-l@uga.edu > Subject: What r the data types in SAS? in Base SAS , and SAS SQL > > Hi all, > What are the different data types in Base SAS and SAS SQL?? character, lengths from 1 to 32,000+ numeric: lengths from 2 to 8 dates are a subtype of numeric and are identified by their date, datetime, or time formats the new proc TSPL (Table Server Processing Language) supports ANSI data types: bigint, tinyint, etc. http://support.sas.com/documentation/cdl/en/tsag/30878/HTML/default/a003 065339.htm http://s...

Re: SAS Job websites WAS(Re: SAS jobs for freshers in US
On Fri, 24 Mar 2006 11:27:44 -0500, Steven Raimi <steven.raimi@ONSTAR.COM> wrote: >Another very helpful (and focused) web site for SAS jobs is >icrunchdata.com. I'll try to ....post from home later, where I have a set of links (I don't have them at my new job here, which, FYI, I found via Monster). I'll also try to avoid fumble-fingering that accidently posts my messages before I'm done writing them! Steve ...

Re: How to call a JAVA API from SAS using BASE SAS Programming
Take a look at Richard DeVenezia's SUGI paper: http://www2.sas.com/proceedings/sugi30/241-30.pdf -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Satya Sent: Friday, July 18, 2008 5:47 AM To: SAS-L@LISTSERV.UGA.EDU Subject: How to call a JAVA API from SAS using BASE SAS Programming Hi All, I need to call a Java API using Base SAS Programming. Please help in this regard. The Java API had been called from Oracle. Now it needs to called from SAS. Thanks in advance. Regards, ...

SAS CONNECT/SAS Display Manager or BASE SAS
Hi, We have SAS EBI Server license. Many or our SAS Programmer wants to use BASE SAS (or SAS Display Manager) instead of SAS Enterprise Guide. But As you know Windows Server comes with only two terminal licenses. I see that we have SAS/CONNECT license (which came with BI suite). Can I install BASE SAS on PC and use SAS/CONNECT to run code on the server? Or Do we have to buy PC license for BASE SAS? What's options we have. Please help. Thanks On Oct 21, 8:11=A0am, abcu...@YAHOO.COM (Abc Unha) wrote: > Hi, > We have SAS EBI Server license. Many or our SAS Programmer wants to =A0us...

SAS Jobs on the SAS Jobs Blog
Many new jobs. For details: http://sasjobs.blogspot.com/ SAS Business Intelligent Consultant Needed APEX Systems is seeking an SAS Business Intelligence consultant for up to an 1 year engagement in Denver, CO. Please let me know if you are interested: lschiola@ apexsystemsinc.com Description: The SAS Administrator/Support Analyst will serve as a key member of the team and will be focused on supporting SAS BI Tools and analytic capabilities. The candidate will be responsible for working with the business partners to jumpstart the region. SAS BI deployment including providing support for for installation, maintenance and troubleshooting of the SAS V9 environment, which includes SAS Metadata Server, SAS Enterprise Business Intelligence Server, SAS Enterprise Guide and Enterprise Miner, SAS Forecast Server, and various other SAS products running on AIX platform. Requirements: -Experience with SAS Business Intelligence Suite 9x - Includes - Enterprise Guide, Information Delivery Portal, Information Map Studio, Web Report Studio -SAS Management Console for SAS platform administration -Detailed understanding of most SAS tools and ability to administer them. - Knowledge of SAS architecture -Planning, ...

Re: SAS macro and SAS Proc SQL
Hi Mini, For SAS macros, I'd highly recommend "Carpenter's Complete Guide to the Macro Language" by Art Carpenter (http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=59224). Hope this helps, Nancy Nancy Brucken brucken@provide.net On Thu, 21 Jul 2005 14:04:53 -0700, Mini <minzheng@COMCAST.NET> wrote: >Any one have some books recommended for learning SAS macros and SAS >Proc SQL? Thanks. ...

Re: EWMA in base SAS / or SAS/STAT?
Oh, someone once prompted me to always post sample data. Here is my sample dataset. I need EWMA for sample var so that it is unique for each site*client*action combination. This maybe a tall order but wanted to check the brain trust just in case. thanks! * build 30 day rolling window test data; data timeSeries; do site_id = '0','1','2','3'; do client ='A','B','C','D'; do action ='type_1', 'type_2', 'type_3'; do date=today()-1 to today()-30; sample=ranuni(123); output; end; end; end; end; format date date7.; run; On 10/11/06, Michael Murff <mysasbox@gmail.com> wrote: > > Hi, > > I'm wondering if others are aware of open source coding for Exponentially > Weighted Moving Average (EWMA) or other advanced time series smoothers (eg. > holt winters) in base SAS? Just putting out a feeler in the hope that I > don't have to reinvent the wheel :-) > > TIA, > > M.M. > ...

Re: SAS ETL studio
Even without ETL Studio it is possible. If the data are in SAS datasets, you have several possibilities in the SAS product set - SAS/Connect, etc. If the data are in external files, it still can be done, but takes a little work - using a set of informats specifically designed for the job - $EBCDIC, S370F..., etc. I hope ETL Studio would make this easier. HTH, Rob Rob Rohrbough Omaha, NE, USA > -----Original Message----- > From: Ankit [mailto:ankit_gangal79@YAHOO.COM] > Sent: Thursday, March 30, 2006 8:58 AM > Subject: SAS ETL studio - EBCIDIC to UNICODE > > > Hi, > > I have to migrate data from mainframes which is in the > extended EBCIDIC > format. Is it possible to have this data migrated from mainframes (and > in extended EBCIDIC format) through SAS ETL studio into flat files and > these flat files be in Unicode or UTF-8 format? > > Thanks, > Ankit > ...

Re: Using Formats in SAS ETL studio
lol, Apologize...I'm reading the iaplanning as I said first and I find this on page 155 : "Working with User-Defined Formats If you have existing SAS data sets, you might also have a catalog of user-defined formats and informats. You have two options for making these formats available to applications such as SAS ETL Studio: The preferred solution is to name the format catalog formats.sas7bcat and to place the catalog in the directory path-to-config-dir\Lev1\SASMain\SASEnvironment\SASFormats. An alternative method of making user-defined formats �visible� is to follow this procedure: 1 Add a line to the configuration file path-to-config-dir\Lev1\SASMain\sasv9.cfg that points to a configuration file for handling user-defined format catalogs. For example, you might add the line -config path-to-config-dir\Lev1\SASMain\userfmt.cfg 2 Then, in the file userfmt.cfg, enter a set statement and a fmtsearch statement. For example, -set fmtlib1 "path-to-config-dir\Lev1\Data\orformat" -fmtsearch (work fmtlib1.orionfmt library) This will make the format catalog orformat.orionfmt available." St�phane. Selon St�phane Colas <saslist@DATAMETRIC.FR>: > Hi, > > doc says : > You do not need to enter metadata for a library that contains custom SAS > formats. However, if a table uses custom formats that are stored in a SAS > library, the library of formats must be available to the SAS application > server > that is used to display data i...

Re: EWMA in base SAS / or SAS/STAT? #4
mysasbox@GMAIL.COM wrote back: >On 10/11/06, David L Cassell <davidlcassell@msn.com> wrote: >> >> >From: Michael Murff <mysasbox@GMAIL.COM> >> >Reply-To: Michael Murff <mysasbox@GMAIL.COM> >> >To: SAS-L@LISTSERV.UGA.EDU >> >Subject: EWMA in base SAS / or SAS/STAT? >> >Date: Wed, 11 Oct 2006 18:57:15 -0600 >> > >> >Hi, >> > >> >I'm wondering if others are aware of open source coding for >>Exponentially >> >Weighted Moving Average (EWMA) or other advanced time series smoothers >>(eg. >> >holt winters) in base SAS? Just putting out a feeler in the hope that I >> >don't have to reinvent the wheel :-) >> > >> >TIA, >> > >> >M.M. >> >>I'm guessing you're still in that SQC project. By now you've probably >>run through enough hours on this that your bosses would have been >>better off licensing SAS/QC. But then you'd have to learn SAS/QC. >> >>EWMA is really easy to do by hand. Pick a weight r. That's it. >> >>Now you have a data set which has a series of means in time order. >>(If you don't have that yet, run your data through PROC MEANS by >>time-variable and get those means.) here's some totally untested >>code you can try: >> >> >>data murff; >>set stringofmeans; >>...

Re: EWMA in base SAS / or SAS/STAT? #2
>From: Michael Murff <mysasbox@GMAIL.COM> >Reply-To: Michael Murff <mysasbox@GMAIL.COM> >To: SAS-L@LISTSERV.UGA.EDU >Subject: EWMA in base SAS / or SAS/STAT? >Date: Wed, 11 Oct 2006 18:57:15 -0600 > >Hi, > >I'm wondering if others are aware of open source coding for Exponentially >Weighted Moving Average (EWMA) or other advanced time series smoothers (eg. >holt winters) in base SAS? Just putting out a feeler in the hope that I >don't have to reinvent the wheel :-) > >TIA, > >M.M. I'm guessing you're still in that SQC project. By now you've probably run through enough hours on this that your bosses would have been better off licensing SAS/QC. But then you'd have to learn SAS/QC. EWMA is really easy to do by hand. Pick a weight r. That's it. Now you have a data set which has a series of means in time order. (If you don't have that yet, run your data through PROC MEANS by time-variable and get those means.) here's some totally untested code you can try: data murff; set stringofmeans; retain r 0.3 ewma 0; if _n_=1 then ewma = yourmean; else ewma = r*yourmean + (1-r)*ewma; run; Pretty easy, huh? Of course, it doesn't stay easy. The control limits are a function of the number of means you have used so far in your computation. They expand away from the centerline monotonically as i (the number of groups used) goes up. The usual formula if you have equa...

Re: SAS Macro to delete a SAS dataset 196997
> From: SAS Techies > Subject: SAS Macro to delete a SAS dataset > > Here's a simple SAS Macro to delete a SAS dataset...It takes the name of > the SAS dataset as the parameter... > > %macro deletedsn(dsname); > > %if %index(&dsname,'.') eq 0 %then %do; %let lib=work; %let > dsn=&dsname; %end; > %else %if %index(&dsname,'.') eq 0 %then %do; %let lib=%scan(&dsname, > 1,'.'); %let dsn=%scan(&dsname,1,'.'); %end; > > proc datasets lib=&lib nolist; > delete &dsn; > qui...

Re: EWMA in base SAS / or SAS/STAT? #3
Thanks for respnse David. I am trying to replicate some Perl code along these lines (sorry, wikipedia again). I am perplexed that I cannot get the weights in either Exponential or Linear algorithms to sum to one, at least not close enough. I am translating from Perl scripts posted at these links, so this could be part of the issue. Basically, I just want to pass in N and generate a vector of weights that sum to one. I cannot seem to achieve this in either case. Anybody want to take a crack at these? * Exponential weights; * http://en.wikipedia.org/wiki/Image:Exponential_moving_average_weights_N%3D15.png; %let N=15; %let f= (1- (2/(&N+1))); %put &f; data _null_; retain total 0; do i=1 to &N; value = (1 - &f) * (&f **i); total= total + value; put i= value= total=; end; run; * Linear Weights; * from http://en.wikipedia.org/wiki/Image:Weighted_moving_average_weights_N%3D15.png ; %let N=15; %let div = (&N * (&N + 1)) / 2; data _null_; retain total 0; do i=1 to &N; if i <= &N -1 then do; value = (&N - i) / &div; end; else do; value = 0; end; total = total + value; put i= value= total=; end; run; On 10/11/06, David L Cassell <davidlcassell@msn.com> wrote: > > >From: Michael Murff <mysasbox@GMAIL.COM> > >Reply-To: Michael Murff <mysasbox@GMAIL.COM> > >To: SAS-L@LISTSERV.UGA.EDU > >Subject: EWMA in base SAS / or SAS/STA...

Re: Opening non SAS files with BASE SAS ?
SYSTASK may be what you want. SYSTASK COMMAND "operating system-command" <WAIT | NOWAIT> <TASKNAME=taskname> <MNAME=name-var> <STATUS=stat-var> <SHELL<="shell-command">>; On 10/10/07, anderson115@gmail.com <anderson115@gmail.com> wrote: > Is there a function that executes a non SAS file? > > I've written a macro that outputs a file with the extention SQL (EXE > would also be helpful). > > *.SQL files are associated with SQL server on my computer so I'd like > to make SAS execute this file so it triggers the SQL server program. > > Is this possible? > > Thank You, > Aaron > ...

SAS Jobs on the SAS Jobs Blog #2
Many new jobs recently posted, see example below. URL: http://sasjobs.blogspot.com SAS BI administration - Challenging opportunity in the Twin Cities metro area. SAS BI administration - Challenging opportunity in the Twin Cities metro area. SAS BI administration skills including the gamut of support activities from installation to troubleshooting and client support activities to architectural support. This is a full time position in the Twin Cities (Minneapolis) metro area. My preference would be for an individual with a strong UNIX background. However, you will be a good fit if you have worked on the SAS EBI suite on any OS. Contact: ...

SAS ETL Job
i am getting error while truncating the table through ETL Job. I created a job using SAS DI Studio and use Load Technique as Refresh->Truncate. But while generating sas code for the same it use the truncate table statement as follows : truncate table "SchemaName".tablename the problem is because of quoates used to enclosed to SchemaName. ...

Re: SAS-ETL
Hi, Have you installed SAS Access to ODBC/Oracle software? If you have installed SAS DI Studio as a separate component, then make sure you have selected SAS Access software too; I guess this error may be because SAS Access may be missing. Thanks, Kiran -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of laliniailaboina2000@GMAIL.COM Sent: Thursday, July 31, 2008 7:15 AM To: SAS-L@LISTSERV.UGA.EDU Subject: SAS-ETL Hi, I am working on mapping of datasets in SAS-ETL which are on ODBC- Microsoft sql server. I have defined source tables and target tables in source and target designer by specifying ODBC as source. After executing the job I am getting error as CLI error trying to establish connection:(microsoft)(ODBC sql server Driver) login failed for user qdr_runtime. and also error in libname statement. Can anybody please explain me whats going on asap. Thanks, Lali ...

Re: sas/etl
On Fri, 6 Oct 2006 06:38:36 -0700, karunakar <mail2karna@GMAIL.COM> wrote: >how to use SAS/ETL software extract,transform,load i need examples can >any one help in this group > >thanks for advance Hi, have you read the documentation on SAS Data Integration Studio (formerly known as SAS ETL Studio)? http://support.sas.com/documentation/onlinedoc/etls/usage33.pdf I believe it is an improved version of the documentation for ETL Studio, which is also on their page. A ...

SAS ETL Studio
Hi there, Can anyone tell me some source to get the basics through in SAS ETL Studio. Thanks & Regards Lokesh Not sure if that's what you're asking for: The doku is under http://support.sas.com/onlinedoc/913/docMainpage.jsp, SAS Data Integration Studio User's Guide (SAS DI) To get started: Best take a course at SAS. HTH Patrick ...

Re: SAS ETL
praveencbharadwaj@GMAIL.COM wrote: >hi, > >I need to use the ETL for multiple data inputs and multiple data >sets.This can be possible in user written code data transform. >Can u please tell me how to use the tool properly for the Multiple >Inputs and Multiple outputs. >Please explain withe steps to follow. > >I was getting an eroor whenever i use splitter and user written code . >User written code can how far be useful. > >plaese help me > >SUNNY. I think that you are going to have to help us before anyone can help you. If you step back, try to forget what you have been working on for a while, and read your post, you will see that it is simply not giving anyone enough information to help you in any substantive way. So: * show us the code you tried, and the error messages you got * tell us what you need to do, with some details * explain what the larger goals of the project are, and why you need this stage of ETL. HTH, David -- David L. Cassell mathematical statistician Design Pathways 3115 NW Norwood Pl. Corvallis OR 97330 _________________________________________________________________ The MSN Entertainment Guide to Golden Globes is here. Get all the scoop. http://tv.msn.com/tv/globes2007/?icid=nctagline2 ...

Re: Using Formats in SAS ETL studio #4
Ankit, The way we have approached this problem is to have the preprocess step of each job include a call to a macro. The macro then sets the format search option. Clint -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of Ankit Sent: Monday, January 16, 2006 2:02 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Using Formats in SAS ETL studio Hi, Does anybody know how to use user defined formats in SAS ETL studio? While mentioning the mapping in the 'LOADER' transformation I am specifying the user defined format. However, I do not know how to specify the location where the SAS ETL would search this user defined format. Under the Base SAS a user can specify the same using the statement : OPTIONS FMTSEARCH = <location> How to achieve the same in SAS ETL studio Thanks & Regards, Ankit ...

Re: Using Formats in SAS ETL studio #2
Also, I found this and I think that you have to specify the format localization only with the path where the format catalog will be find . http://www2.sas.com/proceedings/sugi30/131-30.pdf "STEP 1 Define the necessary library objects. Two library definitions must be specified, one for the source tables and one for the destination table. To define a library object in SAS ETL Studio: 1. Choose the Inventory tab and expand the Foundation repository." Perharps SAS ETL do a fmtsearch with this direclty. St�phane. Selon St�phane Colas <saslist@DATAMETRIC.FR>: > Hi, > >...

Re: Using Formats in SAS ETL studio #3
Hi, doc says : You do not need to enter metadata for a library that contains custom SAS formats. However, if a table uses custom formats that are stored in a SAS library, the library of formats must be available to the SAS application server that is used to display data in the table or to execute code for the table. For details about setting up a SAS format library, see the Post-Configuration Tasks chapter in the SAS Intelligence Platform: Planning and Administration Guide. St�phane. Selon Ankit <ankit_gangal79@YAHOO.COM>: > Hi, > > Does anybody know how to use user defined formats in SAS ETL studio? > While mentioning the mapping in the 'LOADER' transformation I am > specifying the user defined format. However, I do not know how to > specify the location where the SAS ETL would search this user defined > format. Under the Base SAS a user can specify the same using the > statement : > > OPTIONS FMTSEARCH = <location> > > How to achieve the same in SAS ETL studio > > Thanks & Regards, > Ankit > ...

Web resources about - Re: SAS ETL studio - How to call ETL jobs from base/macro SAS - comp.soft-sys.sas

Marketers Studio - David Berkowitz's Marketing Blog
Interactive marketing insight and trends from strategist, columnist, speaker, and marketer David Berkowitz

Abbey Road Studios - Wikipedia, the free encyclopedia
It was established in November 1931 by the Gramophone Company , a predecessor of British music company EMI , its present owner. It is most notable ...

This is the first animated movie that's R-rated and produced by a major Hollywood studio
"Sausage Party" promises to be one of Seth Rogen's raunchiest movies yet — and it's animated! Similar to " Toy Story," it makes inanimate objects ...

Kristen Stewart & Soko take their Parisian love to a ‘studio’: cute or eye-rolly?
... Kristen in the middle of the street. These are photos of Kristen and Soko yesterday. Fame/Flynet says that they were “heading to a studio,” ...

The Walking Dead Attraction Announced For Universal Studios Hollywood
... of the Talking Dead , host Chris Hardwick announced that The Walking Dead will be getting an all-new, year-round attraction at Universal Studios ...

Chris Harris Re-ups Overall Deal With CBS TV Studios, Joins ‘The Great Indoors’ CBS Pilot As Showrunner ...
Comedy writer Chris Harris has extended his overall deal with CBS Television Studios through 2019. Under the pact, Harris will serve as executive ...

WildStar's China launch canceled, Carbine Studios hit with layoffs
It's been a little over half a year since WildStar went free-to-play, but developer Carbine Studios is still going through a tough period. They've ...

Walking Dead Universal Studios attraction becoming permanent - EW.com
You like zombies! You really like zombies! Thanks to popular demand for the undead, Universal Studios Hollywood has announced a permanent residency... ...

China Film Studio Huayi Signs Up Hollywood Execs For International Push
Huayi Brothers, one of China’s largest locally owned film studios, said yesterday it would tap Hollywood executives to lead a push into the international ...

Federal Judge recuses himself from SEC 38 Studios case
... was randomly assigned to the U.S. Securities and Exchange Commission (SEC) case filed against several entities related to the failed 38 Studios ...

Resources last updated: 3/18/2016 3:39:23 PM