11-27-2017 09:42 PM
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?
call execute('select distinct status into :dps from depst;quit;');
if &dps = 'P' then do;
11-27-2017 10:06 PM
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.
11-27-2017 11:39 PM
11-27-2017 11:48 PM
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
11-28-2017 12:01 AM
11-28-2017 01:30 AM
11-28-2017 02:16 AM
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.
11-28-2017 02:21 AM - edited 11-28-2017 02:22 AM
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.
infile datalines dsd truncover;
input JOBNAME:$100. RUNSTATUS:$1. val:32.;
infile datalines dsd truncover;
input JOBNAME:$30. Dependentjobname:$30.;
Thanks and Regards
11-28-2017 06:50 AM
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;