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);
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.
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.
Thanks Saswiki-
That thought did come to my mind-Ok I can certainly write it out to a file.
Lawrence
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.