BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LB
Quartz | Level 8 LB
Quartz | Level 8

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);

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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.

View solution in original post

2 REPLIES 2
SASKiwi
PROC Star

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
Quartz | Level 8 LB
Quartz | Level 8

Thanks Saswiki- 

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

Lawrence 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 528 views
  • 0 likes
  • 2 in conversation