06-30-2015 12:58 PM
I am not certain this the best place for me to ask this question.
Our office is just starting with DI Studio 4.9, and I am the only one using it.
Our system deals with university students.
I am trying to write a job that gets NEW students - only students enrolled in the current / target term.
I have a macro that I used when using Base SAS to accomplish a similar goal. It looked at the current day, compared that to our Terms table and looked for a Term that had a start_date <= current date <= end_date.
This macro returns the Term as well as the Day of the Term - start_date = current_date then Day of Term = 1, etc.
I would like to incorporate this, or something similar into my DI Studio job so that I can use these variables in my DI Studio jobs.
Can someone please point me to the best resource for learning more about this?
Thank you for your time.
07-01-2015 03:59 AM
I still remember my first encounter with DI Studio after many years of SAS coding - and it was first quite challenging and - to be honest - annoying. Make sure you get the necessary training (like: DI Studio Fast Track) before using this tool.
As for your question: You can create a custom transformation and have your macro call in there (an the actual macro either within the transformation or outside in an autocall folder).
07-01-2015 11:44 AM
A year ago, when we first purchased the software, I went to the first training (SAS DI Studio 1: Essentials - not the Fast Track). However, it took a year to get the hardware and get everything installed.
I could definitely use a refresher, but it will have to wait until September of this year, probably.
I'm hoping to get started on some of the basics before then. Thank you.
07-01-2015 06:41 AM
For filtering, use the Extract transform. You enter you filter i the Where tab.
You could use &Sydate, or any other macro variable (that you need to set outside the transformation - where depends on your overall setup).
07-01-2015 06:45 AM
If I try to answer your question more directly: how do you wish to set the value of your "current_day" macro variable?
Macro variables can be set/defined in multiple of ways, most of them are not DI Studio specific (-sysparm, sysget(), value from look-up table, macro logic called from autoexec/job pre-code, parameter to the job) and perhaps a few more....
07-01-2015 10:05 AM
I have written a sloppy macro variable that does the following:
I can copy and paste the macro if that helps - or attach it as a file (it is around 190 lines).
Ideally, I would have these for use throughout multiple jobs.
Thank you all again for the help.
07-01-2015 11:30 AM
If you want these always, call from autoexec_usermods.
Else, call from the pre-step of each job where the resulting macro variables should be used.
Then, just use the macro variables in your (mainly, hopefully) standard Di transformations.
07-01-2015 11:41 AM
I want to call them, always, for a set of jobs.
I have my flow / folder structure broken up into Extract (Jobs and the Tables they produce), Transform (Jobs & Tables), Warehouse_Load (Jobs & Tables).
There are currently 8 jobs in the Extract folder, and at this point - these are the only jobs I see needing access to the created macro variables.
I have never heard of the autoexec_usermods. I have seen the precode / postcode tab of the job properties, but have never used it.
Since these macro variables will span jobs, it seems like a better option to just assume I want them always.
07-02-2015 07:43 AM
appserver_autoexec_usermods are located in the SAS configuration directory, usually in the path (OS, not SAS metadata folders)
07-02-2015 04:07 PM
"There are currently 8 jobs in the Extract folder, and at this point - these are the only jobs I see needing access to the created macro variables."
Given that only a sub-set of jobs needs these macro variables defined AND that it requires "expensive" joining of data I would consider the autoexec the wrong place to execute this.
07-02-2015 04:46 PM
The autoexec is used to "set-up" your environment. It is executed whenever you start a new session or batch-job. You should only place stuff into the autoexec which is required to set-up an environment. If you have a syntax error in the autoexec then SAS won't start up and you can bring down the whole "system".
07-01-2015 06:15 PM
That's may be personal style but I don't like pre- and post-code in DI jobs as they are so hidden. If you want to have a re-usable macro call then I would still go for a custom transformation. You store the macro itself in a SAS Autocall library, you have the macro call in the custom transformation, and if the macro has parameters then you define these parameters as prompts in the custom transformation.
You then simply drag this custom transformation into any job where you need it executed. This way it's easy to use but also clearly visible in the job flow.
Looking at what you describe your macro is doing: Have you considered using a custom interval instead of doing these joins every single time? SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition