topic Conditionally execute precode and postcode in SAS DI studio in SAS Data Management

Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sat, 20 Jun 2020 17:55:17 GMT

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.

I'm looking for an way to control the precode and postcode macro execution by macro variable. E.g.

%let run_macro_pre_post_code=Y; when I need to run precode and postcode and when %let run_macro_pre_post_code=N; then precode and postcode should not run.

/*Precode*/
%job_status(_status=STARTED,                                      
                _jobName=&etls_jobName. 
               );  

/*Postcode*/
%job_status(_status=FINISHED,                                     
                _jobName=&etls_jobName., 
                _job_return_code=&syscc.
   );

I would be thankful if someone of you help me with the logic or the program to accomplish it.

Re: Conditionally execute precode and postcode in SAS DI studio

Shmuel — Sat, 20 Jun 2020 18:02:40 GMT

Add a macro program:

%let run_macro_pre_post_code= Y; /* or N */
%macro chk(flag);
    %if &flag = Y %then %do;
          %job_status( _sattus=STARTED, _jobname= ...);
    %end; %else %do;
         %job_status( _sattus=FINISHED, _jobname= ...);
    %end;
%mend;
%chk(&run_macro_pre_post_code);

Re: Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sat, 20 Jun 2020 18:23:47 GMT

Thank you. It seems you have combined precode and postcode in one macro
'chk'. Is that right or should I use the same approach but separately in
precode and postcode?

Re: Conditionally execute precode and postcode in SAS DI studio

Shmuel — Sat, 20 Jun 2020 18:40:23 GMT

You can submit the macro program once then run:

%macro chk(flag);
    %if &flag = Y %then %do;
          %job_status( _sattus=STARTED, _jobname= ...);
    %end; %else %do;
         %job_status( _sattus=FINISHED, _jobname= ...);
    %end;
%mend;

%chk(Y);
...submit yours body job ...
%chk(N);

Re: Conditionally execute precode and postcode in SAS DI studio

Shmuel — Sat, 20 Jun 2020 19:03:17 GMT

Sorry, it seems that I misunderstood what you are looking for.

On 2nd reading you want to control running or ignoring precode and postcode per run,

then a slight change need:

%macro chk(r_code, flag);
    %if r_code ne Y %then %goto exit;
    %if &flag = 1 %then %do;
          %job_status( _sattus=STARTED, _jobname= ...);
    %end; %else 
    %if &flag = 2 %then %do;
         %job_status( _sattus=FINISHED, _jobname= ...);
    %end;

:exit;
%mend;

%chk(&run_macro_pre_post_code , 1);
...submit yours body job ...
%chk(&run_macro_pre_post_code , 2);

Re: Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sat, 20 Jun 2020 19:12:11 GMT

It appears that you have misunderstood the requirement. If you see my
initial post, I mentioned that I want to run both precode and postcode if
condition is met. That condition can be based on macro variable which
should holds the value either Y or N.

I have over 100 ETL jobs which has both precode and postcode and it same
for all those jobs. With your approach it might be painful to update all
the 100 jobs with the IF condition.

Instead I'm looking for a way how smartly we can add if condition as macro
in separate program and call/use that macro in the job.

With one macro variable, I want to control whether to run the precode and
postcode of the job. It will be helpful when I do some enhancement of the
jobs where I don't want to test my enhance with the pre/post code.

By this job I don't want to redeploy all the 100 jobs and it can be solved
by deploying one new job which has conditional macro.

Re: Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sat, 20 Jun 2020 19:21:12 GMT

Thank you. Any other alternative way to tackle it as this method leads to
update and redeployment of all the ETL jobs?

Re: Conditionally execute precode and postcode in SAS DI studio

Shmuel — Sat, 20 Jun 2020 19:26:46 GMT

Would you prefer make a slight change to your macro: %job_status:

%macro job_status(_status= , _jobname= );
      %if &run_macro_pre_post_code ne Y %then %goto exit;
       ..... your macro code ....

%exit:
%mend job_status;

then just add per job

%let run_macro_pre_post_code= Y; /* or N */

Re: Conditionally execute precode and postcode in SAS DI studio

sas_savvy — Sat, 20 Jun 2020 19:39:54 GMT

Can you elaborate this statement ? What do you mean by executing the Job while it needs an update or enhancement ?

However I don't want this precode and postcode to execute when any of the ETL Job needs an update or enhancement

Re: Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sat, 20 Jun 2020 19:42:11 GMT

It again leads to change of all the pre/post code of the jobs as macro
job_status call is unique across and if I want to change the way for
execution then I need to change in all the jobs.

Re: Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sat, 20 Jun 2020 19:48:11 GMT

Assume I have 100 ETL jobs and all those has same precode and postcode as I
mentioned in the initial post. Now if I want to make changes (e.g.
add/remove transformation) in anyone of the job, then I don't want pre/post
code to run as it's literally a unit testing after enhancement and I don't
want to capture the job status in SQL table.

Ofcourse I will add the pre/post code in. that ETL job, once testing is
done for the changes which I made.

Re: Conditionally execute precode and postcode in SAS DI studio

sas_savvy — Sat, 20 Jun 2020 20:03:19 GMT

Assuming you are making changes to few jobs among the 100 jobs that you have , what is the difficulty in commenting the pre and post code for those few while testing?

Ideally, this type of features need to controlled via an external control tables where you have the full list of Jobs where you have set a Y/N flag to control a behavior for a particular Job.

Re: Conditionally execute precode and postcode in SAS DI studio

Shmuel — Sat, 20 Jun 2020 20:06:51 GMT

Changing macro job_status you need do once only.

Any time you recall the macro to run, in any job, it will use the new/updated macro.

Do you have already defined the statement

%let run_macro_pre_post_code=Y; /* or N */

in the jobs?

if positive then required work is done and no more changes need.

If negative then it depends on the tool you use to submit a set of ETL jobs.

Do you need the macro run_macro_pre_post_code assignment per job?

or per a run of all submitted jobs?

Re: Conditionally execute precode and postcode in SAS DI studio

Patrick — Sat, 20 Jun 2020 23:17:01 GMT

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

Re: Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sun, 21 Jun 2020 07:05:32 GMT

@sas_savvy yes we can comment it but 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.

If you can, please help me with the some example code which determine the way to accomplish it using external tables?

Re: Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sun, 21 Jun 2020 07:10:17 GMT

@Shmuel No, I don't have this statement defined in the job. But I can define it from Scheduler and so no changes are required in the job. As you told, I just have to change the macro definition %Job_status once.

%let run_macro_pre_post_code=Y;

Yes, I need the macro run_macro_pre_post_code assignment per Job.

Re: Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sun, 21 Jun 2020 07:31:28 GMT

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

Re: Conditionally execute precode and postcode in SAS DI studio

Patrick — Wed, 24 Jun 2020 19:21:35 GMT

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.

Re: Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sun, 21 Jun 2020 09:29:12 GMT

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.

Re: Conditionally execute precode and postcode in SAS DI studio

Patrick — Mon, 22 Jun 2020 10:04:54 GMT

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.