BookmarkSubscribeRSS Feed
Sandy10
Calcite | Level 5

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

8 REPLIES 8
Reeza
Super User

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. 

Sandy10
Calcite | Level 5
Hi @Reeza
Yes you are right. My intention is not that. Execution should be parallel. SO dosubl will serve the purpose?Thanks.
Reeza
Super User

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

 

Sandy10
Calcite | Level 5
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;');
Sandy10
Calcite | Level 5
The above query returns below error. Where am i going wrong?

ERROR 72-185: The DOSUBL function call has too many arguments.
SASKiwi
PROC Star

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. 

Sandy10
Calcite | Level 5

Hi @SASKiwi @Reeza,

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

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

s_lassen
Meteorite | Level 14

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 881 views
  • 3 likes
  • 4 in conversation