DATA Step, Macro, Functions and more

How to use call execute macro variable in a if clause in datastep?

Reply
Contributor
Posts: 40

How to use call execute macro variable in a if clause in datastep?

Hi

 

I have this below query. I want to use macro variable dps in if clause which is under a data step. How to use it or we cannot use? 

data _null_;

call execute('select distinct status into :dps from depst;quit;');
if &dps = 'P' then do;

run;

 

Thanks,
Sandhya S

Super User
Posts: 24,026

Re: How to use call execute macro variable in a if clause in datastep?

You can't use it as designed there. CALL EXECUTE executes after the data step ends, the code is generated within the data step. 

Try DOSUBL instead. 

 

I'm also assuming that's not what you're planning to execute, otherwise you should fix that query first. 

Contributor
Posts: 40

Re: How to use call execute macro variable in a if clause in datastep?

Hi @Reeza
Yes you are right. My intention is not that. Execution should be parallel. SO dosubl will serve the purpose?Thanks.
Super User
Posts: 24,026

Re: How to use call execute macro variable in a if clause in datastep?


Sandy10 wrote:
Hi @Reeza
Yes you are right. My intention is not that. Execution should be parallel. SO dosubl will serve the purpose?Thanks.

Try it and let me know Smiley Happy

 

Contributor
Posts: 40

Re: How to use call execute macro variable in a if clause in datastep?

Hi @Reeza

Is it possible to use inter related dosubl like below ? i.e, 2nd dosubl uses data from first dosubl result and 3rd one uses 2nd dosubl results.


rc=dosubl('proc sql;create table dep as select dependentjobname from exeng.execution_engine where job_name="&job1";quit;');
rc=dosubl('proc sql;create table depst as select b.status,a.dependentjobname from dep a inner join tst.status b on a.dependentjobname=b.jobname;quit;');
rc=dosubl('proc sql;select distinct status into :dps separated by ' ' from depst;quit;');
Contributor
Posts: 40

Re: How to use call execute macro variable in a if clause in datastep?

The above query returns below error. Where am i going wrong?

ERROR 72-185: The DOSUBL function call has too many arguments.
Super User
Posts: 4,030

Re: How to use call execute macro variable in a if clause in datastep?

SAS is probably getting confused by the single quotes inside single quotes. Try double inside single instead:

 

rc=dosubl('proc sql;select distinct status into :dps separated by " " from depst;quit;');

Also it would help if you could explain the problem you are trying to solve in more detail. There may be a better way to do what you want rather than assuming CALL EXECUTE or DOSUBL are the best approaches. 

Contributor
Posts: 40

Re: How to use call execute macro variable in a if clause in datastep?

[ Edited ]

Hi @SASKiwi @Reeza,

Below is what I basically require and pasted the code and data also. I am actually stuck Smiley Happy

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

PROC Star
Posts: 277

Re: How to use call execute macro variable in a if clause in datastep?

You have two problems. The first is that the statements in CALL EXECUTE() are not executed before after the datastep. But I assume that you have already changed your code to DOSUBL(), as others have suggested that.

 

The other problem is that the whole datastep is compiled before it is executed, meaning that the macro variable is parsed before the datastep is executed. To get the value assigned by the query, you should use SYMGET to get it:

data _null_;
  rc=dosubl('proc sql;select distinct status into :dps from depst;quit;');
  if symget('DPS') = 'P' then do;

But this is probably not the easiest way to get the values. It is probably easier to create the list of jobs to be run in a new table:

proc sql;
  create table jobs_to_run as select
    check.jobname,
    check.val,
    master.dependentjobname,
    check2.runstatus
  from
    check join master
      on check.jobname=master.jobname
    join check check2
      on master.dependentjobname=check2.jobname
    ;
quit;

You can now just set the table and submit the batchjob if runstatus='P' and val='1'. Of course, you may have to check if you have already submitted the job once:

proc sort data=jobs_to_run;
  by jobname;
run;

data _null_;
  job_already_run=0;
  do until(last.jobname);
    set jobs_to_run;
by jobname; if job_already_run then continue; if val='1' and runstatus='P' then do; /* submit batch job */ job_already_run=1; end; run;
Ask a Question
Discussion stats
  • 8 replies
  • 170 views
  • 3 likes
  • 4 in conversation