topic Re: Query in macro and call execute in SAS Programming

Query in macro and call execute

Sandy10 — Wed, 22 Nov 2017 08:26:54 GMT

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

Re: Query in macro and call execute

AllanBowe — Wed, 22 Nov 2017 08:57:23 GMT

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;

Re: Query in macro and call execute

Kurt_Bremser — Wed, 22 Nov 2017 09:09:16 GMT

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

Re: Query in macro and call execute

Sandy10 — Thu, 23 Nov 2017 03:01:12 GMT

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

Re: Query in macro and call execute

Kurt_Bremser — Thu, 23 Nov 2017 08:20:18 GMT

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

Re: Query in macro and call execute

Sandy10 — Thu, 23 Nov 2017 10:45:20 GMT

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 ?

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

Re: Query in macro and call execute

Kurt_Bremser — Thu, 23 Nov 2017 11:00:47 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Query in macro and call execute

Sandy10 — Fri, 24 Nov 2017 06:32:37 GMT

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

Re: Query in macro and call execute

Sandy10 — Fri, 24 Nov 2017 09:26:02 GMT

Hi. Any update on my above query ? Kindly advise. Thanks.

Re: Query in macro and call execute

Sandy10 — Fri, 24 Nov 2017 09:30:16 GMT

Hi

@Kurt_Bremser
Any update on my above query ? Kindly advise. Thanks.