topic Re: Query in macro and call execute based on sample data in SAS Programming

Query in macro and call execute based on sample data

Sandy10 — Fri, 24 Nov 2017 03:10:54 GMT

Hi,

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 job_name:$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 based on sample data

ChrisNZ — Mon, 27 Nov 2017 00:53:09 GMT

Something like this?

data PGM_LIST;
  input PGM_PARENT $3. PGM_CHILD $6. HAS_RUN ;
  PGM_CHILD    = tranwrd(PGM_CHILD,',',' ');
  DO_BEFORE_RUN= PGM_CHILD;
cards;
P4 P1,P2 0
P1 P2    0
P2       0
run;     

data _null_;                                   
  do while(1);                     
    rc= dosubl('proc sql; select PGM_PARENT into :runthis from PGM_LIST where DO_BEFORE_RUN=" " and HAS_RUN=0; quit;'); 
    if symget('sqlobs') ne '0' then RUNTHIS=symget('runthis');            
    else leave ;
    * call execute(RUNTHIS);  putlog 'Running ' RUNTHIS;      
    rc= dosubl('proc sql; update PGM_LIST set DO_BEFORE_RUN=tranwrd(DO_BEFORE_RUN, '||quote(strip(RUNTHIS))||', " " ); quit;'); 
    rc= dosubl('proc sql; update PGM_LIST set HAS_RUN=1 where PGM_PARENT='||quote(trim(RUNTHIS))||'; quit;');
  end; 
run;

Running P2
Running P1

Running P4

Re: Query in macro and call execute based on sample data

Sandy10 — Mon, 27 Nov 2017 10:59:15 GMT

Hi Chris

If there are more child jobs then i think it will not work as the length will be big to accomodate. Currently its a small table, going forward child job count will increase. Thanks .

Re: Query in macro and call execute based on sample data

Sandy10 — Mon, 27 Nov 2017 10:57:31 GMT

Anybody else has a solution ? I am actually stuck up in else clause.

Thanks and Regards

Sandhya S

Re: Query in macro and call execute based on sample data

ChrisNZ — Mon, 27 Nov 2017 20:11:41 GMT

Is a 32k string not long enough ?

Re: Query in macro and call execute based on sample data

ChrisNZ — Mon, 27 Nov 2017 21:03:36 GMT

Here is a slight variation, a bit more code, but cleaner data storage

data PGM_STATUS;
  input PGM_PARENT $ HAS_RUN ;
cards;
P4 0
P1 0
P2 0
run;  
data PGM_DEPEND;
  input PGM_PARENT $3. PGM_CHILD $3.  ;
  DO_BEFORE_RUN= PGM_CHILD;
cards;
P4 P1 
P4 P2 
P1 P2 
P2    
run;     

data _null_;                                   
  do while(1);                     
    rc= dosubl('proc sql; select s.PGM_PARENT into :runthis 
                          from PGM_STATUS s left join PGM_DEPEND d 
                          on s.PGM_PARENT=d.PGM_PARENT and DO_BEFORE_RUN ne " " 
                          where HAS_RUN=0 
                          having d.PGM_PARENT is null; quit;'); 
    if symget('sqlobs') ne '0' then RUNTHIS=symget('runthis');            
    else leave ;
    * call execute(RUNTHIS);  putlog 'Running ' RUNTHIS;      
    rc= dosubl('proc sql; update PGM_DEPEND set DO_BEFORE_RUN=" " where PGM_CHILD ='||quote(trim(RUNTHIS))||'; quit;'); 
    rc= dosubl('proc sql; update PGM_STATUS set HAS_RUN      = 1  where PGM_PARENT='||quote(trim(RUNTHIS))||'; quit;');
  end; 
run;