topic Re: How to create a macro variable for use in multiple DI Studio jobs in SAS Data Management

How to create a macro variable for use in multiple DI Studio jobs

GregG — Tue, 30 Jun 2015 16:58:21 GMT

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.

Re: How to create a macro variable for use in multiple DI Studio jobs

Patrick — Wed, 01 Jul 2015 07:59:28 GMT

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).

Re: How to create a macro variable for use in multiple DI Studio jobs

LinusH — Wed, 01 Jul 2015 10:41:26 GMT

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).

Re: How to create a macro variable for use in multiple DI Studio jobs

LinusH — Wed, 01 Jul 2015 10:45:57 GMT

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....

Re: How to create a macro variable for use in multiple DI Studio jobs

GregG — Wed, 01 Jul 2015 14:05:38 GMT

I have written a sloppy macro variable that does the following:

Retrieves from the Terms table in our database and retrieves the current semester's value (15/FA, for instance), start_date, and end_date.
Counts the "raw" number of days between these two (raw because it counts at this point weekends and holidays).
Retrieves holidays from the Holidays table, which is joined back in with the local semesters table, and at this point, both holidays and weekends are removed.
Days in the semester are recounted.
At the end of the macro, the following variables are produced - all of these are used in other programs:
- SEM /*FA for Fall, SP for Spring, etc*/
- SemDay /*1 for start_date, etc*/
- TDY /*Two Digit Year, 15 for 2015*/
- RTERM /*Reporting Term - for use in outputting a file*/
- STERM /*Subsetting Term - for use in another program*/
- QTERM /*Querying Term*/
- STC_QTERM /*When querying the STC table*/
- STTR_QTERM /*When querying the STTR table*/
- UFTPT /*The threshold for determining if an undergraduate is full time or part time, which depends on which semester they are in (Summer is different from Fall/Spring)*/
- GFTPT/*Same as above, except for graduate students*/

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.

Re: How to create a macro variable for use in multiple DI Studio jobs

LinusH — Wed, 01 Jul 2015 15:30:20 GMT

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.

Re: How to create a macro variable for use in multiple DI Studio jobs

GregG — Wed, 01 Jul 2015 15:41:51 GMT

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.

Re: How to create a macro variable for use in multiple DI Studio jobs

GregG — Wed, 01 Jul 2015 15:44:02 GMT

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.

Re: How to create a macro variable for use in multiple DI Studio jobs

Patrick — Wed, 01 Jul 2015 22:15:38 GMT

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

Re: How to create a macro variable for use in multiple DI Studio jobs

LinusH — Thu, 02 Jul 2015 11:43:33 GMT

appserver_autoexec_usermods are located in the SAS configuration directory, usually in the path (OS, not SAS metadata folders)

..LevN\SASApp\appserver_autoexec_usermods.sas

Re: How to create a macro variable for use in multiple DI Studio jobs

Patrick — Thu, 02 Jul 2015 20:07:55 GMT

"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.

Re: How to create a macro variable for use in multiple DI Studio jobs

Patrick — Thu, 02 Jul 2015 20:46:11 GMT

@GregG

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".