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);
... View more
