Looking at your log there is a big gap between real time and CPU time. This could be because there is a lot happening on the SQL Server side and SAS is just waiting or because a lot of time gets spent transferring the data from SQL Server to SAS.
Now looking at the code you've posted there are function in it which SAS can't push to the database and for this reason a lot of data will get transferred to SAS for processing ( functions intnx() and input() being the main "problem").
The code also combines Left Join On Clauses with Where Clauses. The Where Clause is likely going to post filter the result set from the Join. It would likely be more efficient to first filter the rows in the source tables to only use the rows for joining which are relevant.
Here my stab at your code. I had to do this "blind" so not very convinced that this will just work (syntax and logic) - but it hopefully will give you some ideas how you could performance tweak the code.
I'm still doing the Group By statement on the SAS side. This because there are variables in the Select clause which are not used for either grouping or aggregation - that's something SAS allows you to do but SQL Server would throw an error. Ideally you don't have such additional variables and can do everything on the SQL Server side.
OPTIONS DEBUG=DBMS_SELECT SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX FULLSTIMER;
/* INITIALIZE GLOBAL VARIABLESS */
%LET begDt= 01MAY2019; /* reporting begin date; FORMAT= DDMMMYYYY*/
%LET endDt= %sysfunc(intnx(month, "&begDt"D , +11, e),date9.); /* reporting end date: FORMAT= DDMMMYYYY*/
%LET begDt_prev=%sysfunc(intnx(month, "&begDt"D , -12, b),date9.);
%LET endDt_prev=%sysfunc(intnx(month, "&endDt"D , -12, e),date9.);
%let sqlsrv_begDt=%unquote(%nrbquote(')&begDt%nrbquote('));
%let sqlsrv_endDt=%unquote(%nrbquote(')&endDt%nrbquote('));
%let sqlsrv_begDt_prev=%unquote(%nrbquote(')&begDt_prev%nrbquote('));
%let sqlsrv_endDt_prev=%unquote(%nrbquote(')&endDt_prev%nrbquote('));
%put &=begDt;
%put &=endDt;
%put &=begDt_prev;
%put &=endDt_prev;
%put &=sqlsrv_begDt;
%put &=sqlsrv_endDt;
%put &=sqlsrv_begDt_prev;
%put &=sqlsrv_endDt_prev;
LIBNAME sqldb odbc complete="driver=SQL Server; database=Epic; server=PHSSQL2195" schema=Finance readbuff=2000 read_lock_type=nolock;
PROC SQL;
connect using sqldb;
CREATE TABLE work.Datapull_epic AS
select distinct *
from connection to sqldb
(
SELECT
H.PatientMRN as MRN
, H.HospitalAccountID as enc
, H.PrimaryPayorID as PayorID
, C.Subscriberid
, C.PlanID
, H.DISCHARGEDTS
, T.HospitalAccountClassDSC
, PT.PatientFirstNM
, PT.PatientLastNM
, PT.BIRTHDTS
, P.PayorNM
, T.flag_exam /** any exam**/
, T.flag_infu /** infusion**/
, T.flag_chemo /** chemotherapy**/
, T.flag_radOnc
, T.flag_immuno /** immunotherapy */
, H.period
, SUM(T.Units) AS units
, SUM(H.TotalChargeAMT) AS charges
(
select
PatientMRN as MRN
, HospitalAccountID
, PrimaryPayorID
, DISCHARGEDTS
, TotalChargeAMT
, CASE
WHEN cast(dischargedts as date)
BETWEEN &sqlsrv_begDt_prev. AND &sqlsrv_endDt_prev. THEN 0 /* Claims data for year prior to report start date */
WHEN cast(dischargedts as date) BETWEEN &sqlsrv_begDt. AND &sqlsrv_endDt. THEN 1
ELSE 2
END
AS period
FROM Fin.HospitalAccount
where
cast(dischargedts as date)
between &sqlsrv_begDt_prev. and &sqlsrv_endDt_prev. /* extract claims from 1 year prior to report start date until report end date */
AND PatientMRN IN
(SELECT DISTINCT A.PatientMRN
FROM Fin.HospitalAccount A
WHERE cast(dischargedts as date) BETWEEN &sqlsrv_begDt. AND &sqlsrv_endDt.
) /* for mrn in cohort 1 period*/
) H
LEFT JOIN Fin.Coverage C
ON H.CoverageID = C.CoverageID
LEFT JOIN Ref.Payor P
ON P.PayorID = C.PayorID
LEFT JOIN
( select
T.HospitalAccountClassDSC
, CASE
WHEN T.hcpcs IN (&examCPT) THEN 1
ELSE 0
END
AS flag_exam /** any exam**/
, CASE WHEN T.hcpcs IN (&infuCPT) OR T.CPT IN (&infuCPT) THEN 1 ELSE 0 END AS flag_infu /** infusion**/
, CASE WHEN T.hcpcs IN (&chemoCPT) OR T.CPT IN (&chemoCPT) THEN 1 ELSE 0 END AS flag_chemo /** chemotherapy**/
, CASE
WHEN T.hcpcs IN (&radOncCPT) THEN 1
ELSE 0
END
AS flag_radOnc
, CASE
WHEN T.hcpcs IN ('C9027','J9271','C9453','J9299','C9284','J9228','C9399','C9483','J9022','C9491','J9023','C9492') THEN 1
ELSE 0
END
AS flag_immuno /** immunotherapy */
, CASE
WHEN T.ChargeModifierListTXT LIKE '%DFP%' THEN 0
ELSE INT(T.TransactionCNT)
END
AS units
from Fin.HospitalTransaction T
where
T.HospitalAccountClassDSC = 'Outpatient' /* outpatient only */
AND T.hcpcs NE ''
) T
ON H.HospitalAccountID = T.HospitalAccountID
LEFT JOIN Pext.PatientExtension PT
ON H.PATIENTMRN = PT.DFCIMRN
)
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
ORDER BY H.PatientMRN , period DESC
;
QUIT;
