Conditionally execute precode and postcode in SAS DI studio

[Patrick]

---

@David_Billa wrote:

I've the ETL Jobs in DI Studio where it has precode and postcode as below. Main purpose of this code to get the Job Status and write in the SQL table.However I don't want this precode and postcode to execute when any of the ETL Job needs an update or enhancement. I don't wish to remove the precode and postcode either.

SAS program for the macro %Job_status is called in autoexec file.

---

The macro definition for %job_status belongs as .sas file into a folder which is part of the SAS Autocall facility and not into an autoexec.

Are you allowed to change anything in the autoexec (btw: any changes belong into autoexec_usermods.sas and NOT autoexec.sas)?

Easiest way would be: 

1. Have the macro definition in an a folder which is part of the SAS Autocall facility (paths defined via option SASAUTOS)

2. Add at the beginning of SASAUTOS a path to your HOME directory (i.e. ~/overlaymacros) - ideally done in autoexec_usermods.sas

3. During development add a .sas file to your HOME directory (i.e. ~/overlaymacros/job_status.sas) with below code in it:

%macro job_status(
    _status=,                                     
    _jobName=, 
    _job_return_code=
   ); 
%mend;

So now what will happen: If in a new DIS session under your user the pre/post code calls the macro then SAS will search for it and first find the macro definition in ~/overlaymacros/job_status.sas. SAS will then compile and execute this "empty shell" macro. Once you're done with your unit testing just remove or rename  ~/overlaymacros/job_status.sas and once you start a new DIS session SAS won't find the macro anymore in your Home directory and though will use the "productive" one. Taking such an approach has the advantage that you don't need to change or comment your pre-code and that you can overlay the macro for your sessions without impact for anyone else.

[David_Billa]

@Patrick I allowed to change autoexec_usermods.sas and NOT autoexec.sas.

In the future we will be adding macros in pore/post code of each and every transformations like we did for Job pre/post code to capture the return code of the transformations in SQL table. In that case it is not easy to comment the pre/post of every tansformation while I update and unit test any of the ETL Job.

e.g.

%let transformName = %nrbquote(Extract_to_pull_IDs); /*Extract_to_pull_IDs  is the value to denote the functional meaning of  the particular step/Transformation and we will passing this values manually*/
%<Macro_to_update_the_SQL_table_with_the _ransformation_Name>;

Also we will be updating the Job pre/post code with one more macro as below alongside %job_status. Here also we will be passing the values manually only during adding this Feature. Once it is done, we will not be touching this value/macro unless it is really required.

%job_audit(_status=STARTED,
               _jobName=&etls_jobName.,      
               _Input_library=<value of Input library>,                       
               _input_table=<value of Input table>               
               _output_library=<value of output library>,
               _output_table=<value of output table>                  
  );

So my objective is to, I don't want any of the pre/post code in job or Transformation to run while I update the Job. Because it's a part of development work and those occasions I don't want anything to update in SQL table.Only during enhancement or update to the Jobs I want to turn off all the pre/post code of the respective job and the transformations involved in that job.

[Patrick · Accepted solution]

You need to clearly distinguish between Macro Definition and Macro Call. The Macro Definition does NOT belong into the DI job but into a separate .sas file stored in a folder which is part of the SAS Macro Autocall Facility. The Macro Call is what belongs into the DIS job.

If you want this for every single transformation then consider to define default parameter values in the Macro Definition or use DIS generated macro variables in the macro wherever possible so you don't have to pass a lot of parameters every time you call the macro - plus you can change the defaults in a single place should there ever be the need.

Now for "disabling" the macros during development: Well, if you've got the macro calls in that many places then having such "overlay" macros as I've already proposed looks to me like a very feasible and easy to manage approach; and it doesn't require any change to the DIS job itself to switch between the productive and the "empty shell" versions.

You might also want to look into Status Handling. The Custom Action there allows to insert macro calls and if done this way then the generated code will also work in case the SAS job throws an Error.

[David_Billa]

May I request you to help me with your approach in one small example?

I'm unable to follow your instructions fully but I will once you tell me
with example.

[Patrick]

There is too much to explain if I have to start from scratch. Can you please be a bit more specific which bit you don't get?

What's important: You need to understand how the SAS Autocall Facility/SAS Autocall Macros work and how SAS finds/compiles macro code for execution when you call it. If you don't know this bit then start reading here and also do some Googling and read some of the Whitepapers.