DATA Step, Macro, Functions and more

Query in macro and call execute

Reply
Contributor
Posts: 39

Query in macro and call execute

Hi

 

Checkmaster is a work table contains attached data . I want to execute one by one jobs under JOBNAME when the val = 0 and runstatus=N  and then update teh status as per status variable in the code using update_status macro. 

My queries are :

 

1. The condition check always is false 

2. Please advise if my call execute statement to include a SAS script is correct or not.

 

 

We are using SAS 9.4 in LINUIX Environment .

 

Below is the code:

 

opitons symbolgen mlogic mprint MINOPERATOR ;
%macro update_status(status=);
proc sql;
update exeng.EngineExecution set runstatus="&status"
where JobName=compress("&&jobs&cnt_h");
quit;
%mend update_status;
%global loc ;
%let loc=/app/ERM/sasdata/logs;

 

%macro jobex;
%let path=/app/scripts/jobs
%let extn=sas;
%global cnt_h;
%global jobs;

proc sql;
select count(jobname) into :cnt_h separated by ' ' from CHECK_MASTER ;
select jobname into :jobs1-:jobs%left(&cnt_h) from CHECK_MASTER;
quit;
data _null_;
set work.CHECK_MASTER;
/*call symputx ("val",val);*/
/*call symputx ("runstatus",runstatus);*/
%do i=1 %to &cnt_h;
%if ((val=0) AND (Runstatus = 'F' or RUNSTATUS='N' )) %then %do;

call execute('%include "&path./&&jobs&i...&extn"');
/*x "&path/&&jobs&i...&extn";*/
status="P";
%end;
%end;
run;
%mend jobex;
%jobex;

 

 

Log:

 

MPRINT(JOBEX): data _null_;
MPRINT(JOBEX): set work.CHECK_MASTER;
SYMBOLGEN: Macro variable CNT_H resolves to 9
MLOGIC(JOBEX): %DO loop beginning; index variable I; start value is 1; stop value is 9; by value is 1.
MLOGIC(JOBEX): %IF condition ((val=0) AND (Runstatus = 'F' or RUNSTATUS='N' )) is FALSE

 

 

Thanks,

Sandhya S

Contributor
Posts: 53

Re: Query in macro and call execute

You are mixing macro and data step logic.  Remember that macro is a code generator - it runs before the boundary of whatever step is being written!

 

Try the following instead:

 

  %let path=/app/scripts/jobs;
  %let extn=sas;
  data _null_;
    set work.CHECK_MASTER;
    if ((val=0) AND (Runstatus = 'F' or RUNSTATUS='N' )) then do;
      call execute('%include "'!!cats("&path/",jobname,".&extn","';"));
       status="P";
    end;
  run;

 

/Allan
Think you know SAS? Take the sasensei challenge..
Super User
Posts: 9,611

Re: Query in macro and call execute

[ Edited ]

This:

%if ((val=0) AND (Runstatus = 'F' or RUNSTATUS='N' )) %then %do;

can NEVER work. You are trying to use SAS dataset variables in macro language, where they simply are not present.

 

The macro PREprocessor does its work when code is fetched for compilation and/or execution. It works only with program text. So it compares the text "val" with the text "0", and this will always end up false. the same is true for the other parts of your conditions. Since you only want to compare data step values, use standard data step code:

if ((val=0) AND (Runstatus = 'F' or RUNSTATUS='N' )) then do;

This is of course just part of your problems. Let's go back to square one and define:

- you have a list of jobs in a dataset, with additional variables

- you want to scan through the dataset and run codes according to those additional variables

Is that right?

If yes, then no macro code is needed. Just use call execute() from the dataset with a simple if.

 

PS you define macro update_status, but you never use it. Do not keep unused code in your programs, as that only causes confusion. Keep your programs neat and tidy.

 

 

Edit: fixed some typos

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 39

Re: Query in macro and call execute

Posted in reply to KurtBremser

Hi

 

Thanks for your replies. It worked, In the same datastep is it possible to check the job's log which is located in a different location and check if there is any error, if error, status ='F' else 'N'.

 

Logs Location : /app/sadata/logs

 

My code is something like this(Initially it was in loop)- Something is definitely wrong here

 

filename oscmd pipe "cd &loc.; ls &&jobs&i.*.log|sort|tail -1";
/* Reading this list of files into a data set. */
data oscmd ;
infile oscmd lrecl=200 truncover;
input file_name $100.;
run;
data _null_;
set oscmd;
call symputx("read",cats("&loc",'/',file_name));
run;
data _null_;
infile "&&read&i" missover truncover;
input;
rc=find(_INFILE_,'Error:'); /* Only read in data set lines. */
if rc ge 1 then
do;
/*%if ((_ERROR_ = 1) && (%sysfunc(sysrc()) = 1) %then %do; */
status="F";

 

Thanks ad Regards

Sandhya S

Super User
Posts: 9,611

Re: Query in macro and call execute

I see room for improvement, the call symputx can be moved into the first data step:

 

data _null_;
infile oscmd lrecl=200 truncover;
input file_name $100.;
call symputx("read",cats("&loc",'/',file_name));
run;

But here

infile "&&read&i" missover truncover;

You use a macro variable that should be named read1, read2, read3 etc, but you only created &read.

Either remove the indirect reference, or make sure that &i is set, and change your call symputx:

call symputx("read&i",cats("&loc",'/',file_name));
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 39

Re: Query in macro and call execute

[ Edited ]
Posted in reply to KurtBremser

Hi

 

I have used rc to get the return code of a job . Now I have another query, if it possible to use proc sql inside call execute ?

 

When val=0, I am executing a set of jobs, when val = 1, then I have to check if its dependents run status if "P", if so then I have to execute the job. Dependents are in different table. Attached the data for your reference.

 

For the Job J_DEF, val =1 , so I have to check MASTER table for the dependents(J_ABC and J_FGH), then again compare with CHECK table to runstatus which is "P" , if "P", the  I have to trigger the job J_DEF  (This part I can use X command)

 

So far, below is the code. I am stuck in else if part i.e, how to check another table and again use the same table . 

This is just a small set of jobs, so I am triggering based on a table  . 

data _null_;
set work.check;
if ((val=0) AND (Runstatus = 'F' or RUNSTATUS='' )) then do;
rc = system( cats("&path/",jobname,".&extn") );
if rc ge 1 then
do;
runstatus="F";
abort;
end;
else do;
runstatus="P";
end;
end;
else if ((val=1) AND (Runstatus = 'F' or RUNSTATUS='' )) then do;
call execute ()

 

Thanks

Sandhya S

Super User
Posts: 9,611

Re: Query in macro and call execute

If CHECKMASTER is a SAS dataset, then post it as such.

Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert it to datastep code, and post the resulting code in a code posting window according to https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce, just as I have done in my examples.

 

Note that Excel files are mostly useless. Since they are unstructured, they cannot convey important information about SAS datasets (variable types, lengths, etc.). Since MS Office files are major transport media for malware, their download from the internet is often blocked by company firewalls; even if no firewall blocks me, I don't download such files, as I don't want to have a serious talk with our auditors.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 39

Re: Query in macro and call execute

[ Edited ]
Posted in reply to KurtBremser

Hi Kurt

 

 

Please find below details.

When val=0, I am executing a set of jobs, when val = 1, then I have to check if its dependents run status if "P", if so then I have to execute the job. Dependents are in different table. Attached the data for your reference.

For the Job J_RM_T_BSRM_MASTER002 in CHECK Table, val =1 , so I have to check MASTER table for the dependents(J_RM_T_BSRM_MASTER001 and J_RM_T_BSRM_MASTER003), then again compare with CHECK table to runstatus which is "P" , if "P", the I have to trigger the job J_RM_T_BSRM_MASTER002 (This part I can use X command)

Please note J_RM_T_BSRM_MASTER001 and J_RM_T_BSRM_MASTER003 will be triggered as ist dependents are with val=0, so it has to be triggered automatically.

This is just a small set of jobs, so I am triggering based on a table .

 

Below is the data for reference.

 

data WORK.CHECK;
infile datalines dsd truncover;
input JOBNAME:$100. RUNSTATUS:$1. val:32.;
datalines4;
J_RM_T_BSRM_MASTER001,,1
J_RM_T_BSRM_CHILD001,,0
J_RM_T_BSRM_CHILD002,,0
J_RM_T_BSRM_CHILD003,,0
J_RM_T_BSRM_MASTER002,,1
J_RM_T_BSRM_MASTER003,,1
J_RM_T_BSRM_CHILD004,,0
J_RM_T_BSRM_CHILD005,,0
J_RM_T_BSRM_CHILD006,,0
;;;;


data WORK.MASTER;
infile datalines dsd truncover;
input JOBNAME:$30. Dependentjobname:$30.;
datalines4;
J_RM_T_BSRM_MASTER001,J_RM_T_BSRM_CHILD001
J_RM_T_BSRM_MASTER001,J_RM_T_BSRM_CHILD002
J_RM_T_BSRM_MASTER001,J_RM_T_BSRM_CHILD003
J_RM_T_BSRM_MASTER002,J_RM_T_BSRM_MASTER001
J_RM_T_BSRM_MASTER002,J_RM_T_BSRM_MASTER003
J_RM_T_BSRM_MASTER003,J_RM_T_BSRM_CHILD004
J_RM_T_BSRM_MASTER003,J_RM_T_BSRM_CHILD005
J_RM_T_BSRM_MASTER003,J_RM_T_BSRM_CHILD006
;;;;

Thanks and Regards
Sandhya S

Contributor
Posts: 39

Re: Query in macro and call execute

Hi. Any update on my above query ? Kindly advise. Thanks.
Contributor
Posts: 39

Re: Query in macro and call execute

Hi

@KurtBremser
Any update on my above query ? Kindly advise. Thanks.
Ask a Question
Discussion stats
  • 9 replies
  • 178 views
  • 0 likes
  • 3 in conversation