Conditionally execute precode and postcode in SAS DI studio

[David_Billa]

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.

[Shmuel]

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

[David_Billa]

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?

[Shmuel]

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

[David_Billa]

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.

[Shmuel]

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

[David_Billa]

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

[Shmuel]

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 */

[David_Billa]

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.

[sas_savvy]

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

[David_Billa]

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.

[sas_savvy]

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.

[David_Billa]

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

[Shmuel]

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?

[David_Billa]

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