Loading submits to GRID via macro

[LB]

So I have a program below  that essentrially breaks up what would be a rather large data pull into smaller ones based on admit dates. This is flowsheet data (everything from vitals-weight, height, how much you urinate, position of bed at meals)-In essence it generates a lot of data.  

I wrote a macro that does actually work per se-but it is only submitting one job at a time vs all 24 simutaneously- 

It is still running mach faster than a normal data pull if I grouped all two years together- However- if I can run all 24 then boom- 

Any suggestions helpful- 

Lawrence 

%let rc=%sysfunc(grdsvc_enable(_all_,resource=NAApp));
data months;

do date = '01JAN2015'd to '31DEC16'd by 1;
dayx=day(date);
startx=intnx('month',date,0);
endx=intnx('month',date,0,'end');
sasdate1=compress("'"||translate(put(startx,yymmdd10.),'-','-')||"'");
sasdate2=compress("'"||translate(put(endx,yymmdd10.),'-','-')||"'");

if dayx=1 then output;
format date mmddyy10.;
keep sasdate1 sasdate2;
end;

run;

%let time3=%SYSFUNC(time(),tod8.);

%macro sendR2 (data=, var=, var1=);
proc sort data=&data(keep=&var &var1) out=values nodupkey ;
by &var &var1;
run;
data _null_;
set values end=last;
call symputx('sx'||left(_n_),&var);
call symputx('sxb'||left(_n_),&var1);



if last then call symputx('countr',_n_,'g');
run;
%put _local_;

%do i=1 %to &countr;
signon sx&i;
%SYSLPUT table=sx&i;
%SYSLPUT start_dx =&&sx&i;
%SYSLPUT end_dx=&&sxb&i;

rsubmit sx&i wait=no;

libname hold '/apps/sas/datasets/xxxx/dev/nc_qos_sandbox/data/CAUTI/';
libname hold1 '/apps/sas/datasets/xxxx/dev/nc_qos_sandbox/data/CAUTI1/';
PROC SQL;
CONNECT TO teradata AS tera(authdomain="Tera_TDP1_HCCLNC_Auth" mode=teradata tdpid=tdp1.kp.org fastload = yes);

CREATE TABLE HOLD.&table as
SELECT * FROM connection to tera
(SELECT DISTINCT
C.pat_enc_csn_id,
A.FLO_MEAS_ID, FLO_MEAS_NAME,
B.FSD_ID,B.LINE, RECORDED_TIME, ENTRY_TIME,
OCCURANCE,
MEAS_VALUE
FROM HCCLNC_USHARE.&sysuserid._CAUTI AS Z
LEFT JOIN pat_enc_hsp C ON Z.PAT_ENC_CSN_ID = C.PAT_ENC_CSN_ID
LEFT JOIN IP_FLWSHT_REC D ON C.INPATIENT_DATA_ID=D.INPATIENT_DATA_ID
LEFT JOIN IP_FLWSHT_MEAS B ON D.FSD_ID=B.FSD_ID
LEFT JOIN IP_FLO_GP_DATA A ON A.FLO_MEAS_ID=B.FLO_MEAS_ID

WHERE
A.FLO_MEAS_ID IN ('12113672','12418219','12418220','12418221','12418222','12418223')
AND CAST(ENTRY_TIME AS DATE) BETWEEN cast(&start_dx aS date) and cast(&end_dx aS date)
ORDER BY C.pat_enc_csn_id, ENTRY_TIME);
QUIT;

LIBNAME USHARE TERADATA
DATABASE=HCCLNC_USHARE
DBINDEX=YES
authdomain="Tera_TDP1_HCCLNC_Auth" mode=teradata
TDPID=tdp1.kp.org
BULKLOAD=YES;

proc sql;

CREATE TABLE USHARE.&table._CAUTIX (FASTLOAD=YES SLEEP=1 TENACITY=15) AS
SELECT DISTINCT PAT_ENC_CSN_ID,OCCURANCE
from HOLD.&table;

PROC SQL;
CONNECT TO teradata AS tera(authdomain="Tera_TDP1_HCCLNC_Auth" mode=teradata tdpid=tdp1.kp.org fastload = yes);

CREATE TABLE HOLD1.&table as
SELECT * FROM connection to tera
(SELECT DISTINCT
C.pat_enc_csn_id,
A.FLO_MEAS_ID, FLO_MEAS_NAME,
B.FSD_ID,B.LINE, RECORDED_TIME, ENTRY_TIME,
B.OCCURANCE,
MEAS_VALUE
FROM HCCLNC_USHARE.&table._CAUTIX AS Z
LEFT JOIN pat_enc_hsp C ON Z.PAT_ENC_CSN_ID = C.PAT_ENC_CSN_ID
LEFT JOIN IP_FLWSHT_REC D ON C.INPATIENT_DATA_ID=D.INPATIENT_DATA_ID
LEFT JOIN IP_FLWSHT_MEAS B ON D.FSD_ID=B.FSD_ID
LEFT JOIN IP_FLO_GP_DATA A ON A.FLO_MEAS_ID=B.FLO_MEAS_ID AND Z.OCCURANCE=B.OCCURANCE

WHERE
A.FLO_MEAS_ID IN ('900700','900701','900702','900703')
ORDER BY C.pat_enc_csn_id, ENTRY_TIME);
QUIT;

endrsubmit;
waitfor _all_ sx&i;

signoff _all_;

%end;
%mend;
%sendR2(data=months, var=sasdate1, var1=sasdate2);

[SASKiwi · Accepted solution]

Doing RSUBMITs inside macros can be problematic and I wouldn't recommmend it as the remote sessions know nothing about your macro and SAS can get confused where step boundaries are. A better approach would be to get the macro to write a file containing "normal" SAS code containing the RSUBMIT blocks, then %INCLUDE that later outside of all macro processing.

[LB]

Thanks Saswiki- 

That thought did come to my mind-Ok I can certainly write it out to a file.

Lawrence