adding to an existing code - I put in bold the measure I am trying to add in the summary macro

[ljw4]

Options mprint pageno=1 nocenter source cleanup;
options symbolgen;

libname dataset 'H:\SASUSER\Clin_Anal\Monthly_Datasets\TMP\2020';
*libname dataset 'H:\SASUSER\Clin_Anal\Monthly_Datasets\TMP\2017\test';

%let report_location = \\Cifs2\opsdata$\Laura\TMP\TMP Dashboard 2020;
*%let report_location = \\Cifs2\datapchi$\Utilization Reports\TMP Inpatient Dashboard\2017\test;

%let filename =; * Only used in naming Excel report. SHOULD BE NULL FOR PRODUCTION!!!!!! ;

*----------------------------------*
* User Defined Program Parameters *
*----------------------------------*;

* Date parms updated 01.03.2019 ;

%let Start_dt = 01JAN2020; * Start Date for Reporting Period - Change at beginning of year only.;
%let End_dt = 30jun2020; * End Date for Reporting Period (Change Monthly). Stop at end of CY.;
%let Pdapp_dt = 31aug2020; * Paid thru: 60d lag, same date used for prior year (Change Monthly)*;
%let pri_Start = 01JAN2019; * Prior Start Date for Reporting Period -Change at beginning of year only.;
%let pri_End = 30jun2019; * Prior End Date for Reporting Period (Change Monthly). Stop at end of Prior CY.;
%let no_mos = 6; * No of incurred months, used for avg mm calc. Corresponds with End Month. Change Monthly but stop at 12 (Dec).*;
* Change IBNR monthly based on IBNR Trends 2011-2019.xlsx. *;
%let ibnr = 1.010; /*Use IBNR from the HSF AVG column on the spreadsheet that corresponds to the incurred month */

%let pri_YR = 2019; * Change at beginning of year only.;
%let cur_YR = 2020; * Change at beginning of year only.;

%let RSO_grps = ('011','103','021','020','019','105','189','195','196','198','415'); /*11.01.2016, add nshs rowley. 07.06.2017, eliminate 102. /*6/5/19 - added 189 mystic*/ /*add NSHS ipswich 198 for 2020*/
/*06.06.2017, take out 064 HHPHO*/
/*2016_changes, the RSO_grps filter had to be added, otherwise wrong results may potentially occur
when the database adds new RSOIDs without notice. This filter will limit the report to only the
RSOs that are expected in report. */


data _null_;
cytd_start = "'"||put("&Start_dt"d,date9.)||"'";
cytd_End = "'"||put("&End_dt"d,date9.)||"'";
cytd_pd = "'"||put("&Pdapp_dt"d,date9.)||"'";
pytd_start = "'"||put("&pri_Start"d,date9.)||"'";
pytd_End = "'"||put("&pri_End"d,date9.)||"'";

call symput('cytd_start' , trim(left( cytd_start)));
call symput('cytd_End' , trim(left( cytd_End)) );
call symput('cytd_pd' , trim(left( cytd_pd)) );
call symput('pytd_start' , trim(left( pytd_start)));
call symput('pytd_End' , trim(left( pytd_End)) );

put (_all_) (=/);
run;

*-----------------------------------------------------------------*;
* Extract Data from Oracle - Only if data hasn't been extracted *;
* or you change a parameter that doesn't require a re-extraction *;
* *;
* 8.2010 - Use tos_cd instead of actrl_cd to extract Inpatient *;
* and board. *;
*-----------------------------------------------------------------*;

Proc sql;
connect to odbc(user=xxxx password=xxxxx dsn=EDWProduction);
*** Extract Medical Inpatient Claims From EDW ***;

Create table work.Med1a as
Select *
From
(
Select
PlanCD
, ReportGroupID
, RSOID
, PlanPCPID
, MemberID
, PlanProviderID
, type
, Service
, cat(Service,PlanProviderID) as prov_adm_type
, YearOfServiceNBR
, AdmissionDTS
, datepart(AdmissionDTS)+abs(days) format=date9. as DischargeDTS
, Cost
, Allowed01AMTCost
/*Allowed01AMT_change*/
, Days
, DRG
, Diagnosis01CD
From
connection to odbc
( select
PlanCD
, ReportGroupID
, case when RSOID='065' then '064' else RSOID end as RSOID /*2015_Program will keep this code from 2014*/
, PlanPCPID
, MemberID
, PlanProviderID
, case when TypeOfServiceCD in ('2200','2210','2220') then 'Acute' else 'Non-Acute' end as Type/*12.13.2017*/
, case when TypeOfServiceCD in ('2200','2210','2220') then 'Inpatient Room & Board'/*12.13.2017*/
when ActuarialCD='9002' then 'Psych Inpatient Rm & Bd'
when ActuarialCD='9003' then 'Rehab Room & Board'
when ActuarialCD='9004' then 'Snf Room & Board' end as Service
, cast( year(AdmissionDTS) as char(4) ) as YearOfServiceNBR
, AdmissionDTS
, sum(Allowed02AMT) as Cost
, sum(Allowed01AMT) as Allowed01AMTCost
/*Allowed01AMT_change*/
, sum(ServiceQTY) as Days
, max(DRG) as DRG
, max(Diagnosis01CD) as Diagnosis01CD
from payer.healthplan.Medical
where (ActuarialCD in ('9002','9003','9004') or TypeOfServiceCD in ('2200','2210','2220')) /*12.13.2017*/
and PlanCD='SECH'
and ProductTypeCD='HMO'
/* and RSOID not in('05','17','028','031','032','221','009','010') */
/* 6.2014 Exclude BMA 010; 5.2013 exclude prima and hawthorne 6.2012 - Exclude CHA and CMPC */
and RSOID not in('05','17','028','031','032','221','009','010','180','116','117','118','033','093')
and RSOID in &RSO_grps. /*2016_changes, adding filter to limit RSOIDs to those expected. */
/*2015_Program exclude 180 CAMC, 116 CRMA-FR, 117 CRMA-NA, 118 CRMA-MO, 033 PMG */
and AdjudicationCD Not Like '%D%'
and FundCD='HS'
and PlanCD='SECH'
and ((AdmissionDTS Between &cytd_Start and &cytd_End and (PaidDTS Is Null or PaidDTS <= &cytd_Pd )and SettlementYearNBR<>'CPAS')
or (AdmissionDTS Between &pytd_Start and &pytd_End and ( PaidDTS Is Null or PaidDTS <= &cytd_Pd) and SettlementYearNBR<>'CPAS' ))
group by PlanCD, ReportGroupID, RSOID, PlanPCPID, MemberID, PlanProviderID
, case when TypeOfServiceCD in ('2200','2210','2220') then 'Acute' else 'Non-Acute' end/*12.13.2017*/
, case when TypeOfServiceCD in ('2200','2210','2220') then 'Inpatient Room & Board' /*12.13.2017*/
when ActuarialCD='9002' then 'Psych Inpatient Rm & Bd'
when ActuarialCD='9003' then 'Rehab Room & Board'
when ActuarialCD='9004' then 'Snf Room & Board' end
, cast( year(AdmissionDTS) as char(4) )
, AdmissionDTS
having sum(ServiceQTY)>0) );

* Extract Member Months From Oracle *;
Create table Work.Mm1 as
Select *
From connection to odbc
( Select PlanCD, NetworkContractCD, YearNBR, ReportGroupID,
case when RSOID='065' then '064' else RSOID end as RSOID, /*2015_Program will keep this code from 2014*/
PlanPCPID,
Sum(JanuaryMemberCNT) as M_1, Sum(FebruaryMemberCNT) as M_2, Sum(MarchMemberCNT) as M_3, Sum(AprilMemberCNT) as M_4,
Sum(MayMemberCNT) as M_5, Sum(JuneMemberCNT) as M_6, Sum(JulyMemberCNT) as M_7, Sum(AugustMemberCNT) as M_8,
Sum(SeptemberMemberCNT) as M_9, Sum(OctoberMemberCNT) as M_10, Sum(NovemberMemberCNT) as M_11, Sum(DecemberMemberCNT) as M_12
From payer.healthplan.MemberMonthPCP
Where
ProductTypeCD = 'HMO' and
PlanCD='SECH' and
YearNBR Between &Pri_Yr and &Cur_Yr and
/* RSOID not in('05','17','028','031','032','221','009','010') */
/* 6.2014 Exclude BMA 010 */
/* 5.2013 exclude prima and hawthorn 6.2012 - Exclude CHA and CMPC */
RSOID not in('05','17','028','031','032','221','009','010','180','116','117','118','033','093')
and RSOID in &RSO_grps. /*2016_changes, adding filter to limit RSOIDs to those expected. */
/*2015_Program exclude 180 CAMC, 116 CRMA-FR, 117 CRMA-NA, 118 CRMA-MO, 033 PMG */

Group by PlanCD, NetworkContractCD, YearNBR, ReportGroupID, RSOID, PlanPCPID );
disconnect from odbc;
quit;

* 8.2010 - The Service variable needs to be longer than 23 characters in order to accomodate
the Observation actuarial description. ;
data med1b (drop=Service1);
set med1a (rename=(Service=Service1));
length Service $30;
Service=Service1;
run;

/*Bridge claims that are split billed */
proc sort data = Med1b;
by MemberID prov_adm_type AdmissionDTS DischargeDTS;
run;

data no_combine combine;
set Med1b;
by MemberID prov_adm_type;
if first.prov_adm_type=1 and last.prov_adm_type=1 then output no_combine; else output combine;
run;
data no_combine;
set no_combine;
format AdmissionDTS1 date9.;
AdmissionDTS1=datepart(AdmissionDTS);
drop AdmissionDTS;
rename AdmissionDTS1=AdmissionDTS;
run;
data no_combine;
retain PlanCD ReportGroupID RSOID PlanPCPID MemberID PlanProviderID prov_adm_type service type YearOfServiceNBR DischargeDTS AdmissionDTS days cost;
set no_combine;
run;

proc sort data = combine;
by MemberID prov_adm_type AdmissionDTS;
run;

* Number each stay (record) for a given MemberID, service, PlanProviderID;
data combine_1;
set combine;
by MemberID prov_adm_type;
retain n;
format n 6.;

if first.prov_adm_type then n=1;
else n+1;
run;

* Create a dataset from which the 1st stay for each MemberID/service/PlanProviderID has been removed;
data combine_firstobs2;
set combine;
by MemberID prov_adm_type;
if first.prov_adm_type then delete;
run;

proc sort data=combine_firstobs2;
by MemberID prov_adm_type AdmissionDTS;
run;

* Number each stay.
(What had been number 2 in the combine_1 dataset, is now number 1, what had been number 3 is now number 2, etc.) ;
data test2(keep= MemberID prov_adm_type n lead_adm lead_dis);
set combine_firstobs2;
by MemberID prov_adm_type;
format n 6.;
retain n;
if first.prov_adm_type then n=1;
else n+1;
rename AdmissionDTS = lead_adm
DischargeDTS = lead_dis;
run;

proc sort data = combine_1;
by MemberID prov_adm_type n;
run;
proc sort data = test2;
by MemberID prov_adm_type n;
run;

* For each stay (record), include the AdmissionDTS and DischargeDTS of the next stay for a given MemberID/service/PlanProviderID ;
data test3;
merge combine_1 test2;
by MemberID prov_adm_type n;
run;

* Off_day is the days between the discharge date of one stay and the AdmissionDTS of the next stay for a given
MemberID/service/PlanProviderID ;
* lag_day contains the off_day calculated on the previous stay.
lag_day is the days between the previous stay's DischargeDTS and the AdmissionDTS on this stay. ;
/*
Example from test4: Note, there is no gap between the DischargeDTS of stay1 and the AdmissionDTS of stay2.
PLANCD REPGRP RSOID PLANPCPID MEMBERID PLANPROVIDERID TYPE PROV_ADM_TYPE YEAROFSERVICENBR ADMISSIONDTS DISCHARGEDTS COST DAYS Service n lead_adm lead_dis flag off_day lag_day
SECH 16 118 712571 S00063771 900020 Acute Inpatient Room & Board900020 2010 1/9/2010 1/22/2010 18029.29 13 Inpatient Room & Board 1 1/22/2010 1/27/2010 1 0
SECH 16 118 712571 S00063771 900020 Acute Inpatient Room & Board900020 2010 1/22/2010 1/27/2010 10491.36 5 Inpatient Room & Board 2 2/21/2010 2/27/2010 1 -25 0
SECH 16 118 712571 S00063771 900020 Acute Inpatient Room & Board900020 2010 2/21/2010 2/27/2010 11558.41 6 Inpatient Room & Board 3 2 -1 -25
*/

data test4;
set test3;
by MemberID prov_adm_type;
format flag 6.;
retain flag;

off_day = DischargeDTS - datepart(lead_adm);
lag_day = lag(off_day);

if last.prov_adm_type and lag_day=0 then off_day=0;
if last.prov_adm_type and lag_day^=0 then off_day=-1;

if first.prov_adm_type then flag=1;
else if lag_day^=0 then flag +1;
run;

proc sort data = test4;
by MemberID prov_adm_type flag;
run;

/* Example from test5: Note that the first 2 stays are combined, since there was no gap. The 1st AdmissionDTS and the 2nd DischargeDTS are now on one record. Tot_days and Tot_Cost were combined for these stays.
PLANCD REPGRP RSOID PLANPCPID MEMBERID PLANPROVIDERID TYPE PROV_ADM_TYPE YEAROFSERVICENBR ADMISSIONDTS DISCHARGEDTS COST DAYS Service n lead_adm lead_dis flag off_day lag_day retain_adm AdmissionDTS1 tot_days tot_cost
SECH 16 118 712571 S00063771 900020 Acute Inpatient Room & Board900020 2010 1/22/2010 1/27/2010 10491.36 5 Inpatient Room & Board 2 2/21/2010 2/27/2010 1 -25 0 1/9/2010 1/9/2010 18 28520.65
SECH 16 118 712571 S00063771 900020 Acute Inpatient Room & Board900020 2010 2/21/2010 2/27/2010 11558.41 6 Inpatient Room & Board 3 2 -1 -25 2/21/2010 2/21/2010 6 11558.41
*/
data test5;
set test4;
by MemberID prov_adm_type flag;
format retain_adm AdmissionDTS1 date9.;
retain retain_adm tot_days tot_cost;

if first.flag then do; retain_adm = datepart(AdmissionDTS); tot_days=days; tot_cost=cost; end;
else do;
tot_days + days;
tot_cost + cost;
end;
if last.flag then do; AdmissionDTS1 = retain_adm; output; end;

run;

data test6;
set test5;
drop n lead_adm lead_dis flag off_day lag_day retain_adm AdmissionDTS days cost;
rename AdmissionDTS1=AdmissionDTS
tot_days=days
tot_cost=cost;
run;

data final;
set test6 no_combine;
run;

proc sort data = final;
by MemberID prov_adm_type AdmissionDTS DischargeDTS;
run;

* At this point, it is not likely for there to be more than one record for a MemberID/prov_adm_type/AdmissionDTS. ;
data med1_fin;
set final(drop=days);
AdmitID=MemberID||prov_adm_type||AdmissionDTS;
by MemberID prov_adm_type AdmissionDTS;

if last.AdmissionDTS then do; days=DischargeDTS - AdmissionDTS; output;end;
run;

/*Find AdmitIDs are considered readmissions */
/* Readmission is for Acute admissions only where the subsequent admit was 30 days from the original */
/* Admissions that are considered transfers (the subsequent AdmissionDTS=the original DischargeDTS
or the subsequent AdmissionDTS = the original AdmissionDTS) are removed because they are not considered a readmit*/
/* (clinically, they are considered to be part of the same admission) */
proc sort data=med1_fin;
by MemberID AdmissionDTS DischargeDTS;
quit;
/*
Example from med1_readm1 where ip_flag is set to 2:
The 2nd record won't be counted as a SeptemberMemberCNTarate admit, because AdmissionDTS = prior_DischargeDate so ip_flag =2, even though the PlanProviderID is different.
The transfer was from BOSTON MEDICAL CENTER to WINCHESTER HOSPITAL.
PLANCD REPORTGROUPID RSOID PLANPCPID MEMBERID PLANPROVIDERID TYPE PROV_ADM_TYPE YEAROFSERVICENBR DISCHARGEDTS Service AdmissionDTS cost AdmitID days prior_DischargeDate prior_AdmitDate prior_AdmitID los ip_cnt ip_flag
SECH 11 010 074625 S00012095 901923 Acute Inpatient Room & Board901923 2010 1/15/2010 Inpatient Room & Board 1/14/2010 18163.16 S00012095 Inpatient Room & Board901923 18276 1 . . . 1 1 .
SECH 11 010 074625 S00012095 904244 Acute Inpatient Room & Board904244 2010 1/28/2010 Inpatient Room & Board 1/15/2010 61982.76 S00012095 Inpatient Room & Board904244 18277 13 1/15/2010 1/14/2010 S00012095 Inpatient Room & Board901923 18276 13 0 2

Example from med1_readm1 where ip_flag is set to 1:
The 2nd record won't be counted as a SeptemberMemberCNTarate admit, because AdmissionDTS = prior_AdmitDate.
The transfer was from BOSTON MEDICAL CENTER to WINCHESTER HOSPITAL.-
PLANCD REPORTGROUPID RSOID PLANPCPID MEMBERID PLANPROVIDERID TYPE PROV_ADM_TYPE YEAROFSERVICENBR DISCHARGEDTS Service AdmissionDTS cost AdmitID days prior_DischargeDate prior_AdmitDate prior_AdmitID los ip_cnt ip_flag
SECH 11 010 771673 S00384992 901923 Acute Inpatient Room & Board901923 2010 3/2/2010 Inpatient Room & Board 3/1/2010 3082.71 S00384992 Inpatient Room & Board901923 18322 1 . . . 1 1 .
SECH 11 010 771673 S00384992 904244 Acute Inpatient Room & Board904244 2010 3/2/2010 Inpatient Room & Board 3/1/2010 14847.92 S00384992 Inpatient Room & Board904244 18322 1 3/2/2010 3/1/2010 S00384992 Inpatient Room & Board901923 18322 0 0 1
*/
data med1_readm1;
set med1_fin;
if type='Acute';
by MemberID;

prior_DischargeDate = lag(DischargeDTS); format prior_DischargeDate date9.;
prior_AdmitDate = lag(AdmissionDTS); format prior_AdmitDate date9.;
prior_AdmitID=lag(AdmitID);
los = DischargeDTS - AdmissionDTS;
if first.MemberID then
do;
prior_DischargeDate = .;
prior_AdmitDate = .;
prior_AdmitID=.;
ip_cnt =1;
end;
if first.MemberID ^=1 and AdmissionDTS = prior_AdmitDate then
do;
ip_flag =1;
los =0;
end;
if AdmissionDTS = prior_DischargeDate then ip_flag =2;

if ip_flag in (1,2) then ip_cnt =0; else ip_cnt=1;
run;
data med1_readm2 (drop=prior_AdmitDate prior_DischargeDate prior_AdmitID ip_cnt los ip_flag) ;
set med1_readm1;
where ip_cnt=1;
run;
/* Now that transfers are removed, identify readmits */
proc sort data = med1_readm2;
by MemberID AdmissionDTS DischargeDTS;
run;
data re_admit;
set med1_readm2;
by MemberID AdmissionDTS;

AdmID = _n_ ;
prior_DischargeDate = lag(DischargeDTS); format prior_DischargeDate date9.;

if first.MemberID then re_admit =0;
if first.MemberID ^=1 and (AdmissionDTS - prior_DischargeDate) <=30 then re_admit =1;
else re_admit =0;
run;

data readmit_fin (keep=AdmitID);
set re_admit;
if re_admit=1;
run;
proc sql;
create table med1_fin_new as
select a.*, case when b.AdmitID is not null then 1 else 0 end as readmit_flag
from med1_fin a
left join
(select distinct AdmitID
from readmit_fin) b
on a.AdmitID=b.AdmitID;
quit;
data med1_fin_new;
set med1_fin_new;
if days=1 and type='Acute' then one_day_flag=1;
else one_day_flag=0;
AdmitID2=trim(MemberID)||trim(PlanProviderID)||
put(year(AdmissionDTS),4.) || '/' || put(month(AdmissionDTS),z2.) ||
'/' || put(day(AdmissionDTS),z2.);
run;

/*Save patient level detail */
data dataset.tmp&start_dt._&end_dt._&Pdapp_dt.;
set med1_fin_new;
run;

/*For observation logic */
Proc sql;
connect to odbc(user=xxxx password=xxxxx dsn=EDWProduction);
*** Extract Observation records ***;
Create table work.Med_obs1_pre as /*March_2016_changes, changed table name to Meds_obs_pre, so it can be re-named its original name of Meds_obs*/
Select PlanCD,NetworkContractCD,ReportGroupID,RSOID,ClaimNBR as Grp_Var,PlanPCPID,MemberID,PlanProviderID,propcase(Service) as service,'Acute' as Type,
DiagnosisCategoryCD, /*March_2016_changes*/
Pd_Year,
YearOfServiceNBR, Datepart(AdmissionDTS) as AdmissionDTS, AdmissionDTS as AdmissionDTS2, Cost,Days
From connection to odbc (
Select Medical.PlanCD,NetworkContractCD,ReportGroupID,RSOID,ClaimNBR,PlanPCPID,MemberID,PlanProviderID,
Actuarial.ActuarialDSC as Service,AdjudicationCD,
Medical.DiagnosisCategoryCD, /*March_2016_changes*/
year(PaidDTS) as Pd_Year,YearOfServiceNBR, Min(AdmissionDTS) as AdmissionDTS,
Sum(Allowed02AMT) as Cost,Sum(ServiceQTY) as Days
From payer.healthplan.Medical
left join payer.reference.Actuarial on Medical.ActuarialCD=Actuarial.ActuarialCD
Where Medical.ActuarialCD In ('9011')
/* and SUBSTRING(Medical.DiagnosisCategoryCD, 1 ,CHARINDEX('.', Medical.DiagnosisCategoryCD, 1)-1) not in ('5','11')*/ /*March_2016_changes*/
and Medical.PlanCD='SECH'
and ProductTypeCD='HMO'
and RSOID not in('05','17','028','031','032','221','009','010','180','116','117','118','033')
and AdjudicationCD Not Like '%D%'
and FundCD='HS'
and Actuarial.PlanCD='SECH'
and ((AdmissionDTS Between &cytd_Start and &cytd_End and
( PaidDTS Is Null or PaidDTS <= &cytd_Pd ) and SettlementYearNBR<>'CPAS') or
(AdmissionDTS Between &pytd_Start and &pytd_End and
( PaidDTS Is Null or PaidDTS <= &cytd_Pd) and SettlementYearNBR<>'CPAS' ))
Group By Medical.PlanCD,NetworkContractCD,ReportGroupID,RSOID,ClaimNBR,PlanPCPID,MemberID,PlanProviderID,
PaidDTS,Actuarial.ActuarialDSC,AdjudicationCD,
Medical.DiagnosisCategoryCD, /*March_2016_changes*/
year(PaidDTS),YearOfServiceNBR );

disconnect from odbc;
quit;

/*March_2016_changes,
We are creating a new table to specifically handle the issue with substring(charindex()) being used with the SQL pass through.
In this new table, we will make sure that the claims in question do not have the DiagnosisCategoryCD's in question. We also
need to sum up and group by the same variables that were summed and grouped by in the previous version of the Med_obs1 table. (When
the table ran without errors.)*/
Proc sql;
Create table work.Med_obs1 as
Select PlanCD,NetworkContractCD,ReportGroupID,RSOID,Grp_Var,PlanPCPID,MemberID,PlanProviderID,service,Type,
Pd_Year,
YearOfServiceNBR, Min(AdmissionDTS) as AdmissionDTS, AdmissionDTS2, Sum(Cost) as Cost,Sum(Days) as Days
from work.Med_obs1_pre
where
SUBSTR(DiagnosisCategoryCD, 1 ,find('.', DiagnosisCategoryCD, 1)-1) not in ('5','11')

Group By PlanCD,NetworkContractCD,ReportGroupID,RSOID,Grp_Var,PlanPCPID,MemberID,PlanProviderID,service,Type,
Pd_Year, YearOfServiceNBR ;
quit;
/*March_2016_changes, end of new table
------------------------------------------------------------------------------------------------------------------*/

/*Save patient level detail */
/*data dataset.obs&start_dt._&end_dt._&Pdapp_dt.;
set Med_obs1;
run;
*/
*-----------------------------------------------*;
* Create Admits using "User Defined Variables" *;
*-----------------------------------------------*;
Proc sql;
Create table work.Med2a as
Select distinct
ReportGroupID
, RSOID
, YearOfServiceNBR
, Type
, Service
, count(*) as admits
, sum(readmit_flag) as readmits
, sum(one_day_flag) as oneday_admits
, Sum(Days) as Days
, Sum(Cost) as Cost
From med1_fin_new
Group By ReportGroupID,RSOID,YearOfServiceNBR,Type,Service ;
Quit;

/*Observation summary */
Proc sql;
Create table work.Med_obs2 as
Select
ReportGroupID
, RSOID
, YearOfServiceNBR
, type
, Service
, Sum(Admits) as Admits,
Sum(Cost) as Cost
From ( Select ReportGroupID,RSOID,YearOfServiceNBR,Type,Service,MemberID,PlanProviderID,AdmissionDTS,1 as Admits,
Sum(Days) as Days,Sum(Cost) as Cost
From ( Select YearOfServiceNBR,Type,Service,MemberID,PlanProviderID,AdmissionDTS,Days,Cost,ReportGroupID,RSOID
From ( Select NetworkContractCD,ReportGroupID,RSOID,MemberID,PlanProviderID,AdmissionDTS,Type,Service,
YearOfServiceNBR,Cost,Days
From work.Med_obs1 ) )
Group By ReportGroupID,RSOID,YearOfServiceNBR,Type,Service,MemberID,PlanProviderID,AdmissionDTS)
Where Cost >0
Group By ReportGroupID,RSOID,YearOfServiceNBR,Type,Service ;
Quit;
data med2;
set med2a
/*Med_obs2*/ /*06.30.16 No longer need Observation information.*/
;
run;
*-----------------------------------------------------*;
* Group member Months using "User Defined Variables" *;
*-----------------------------------------------------*;
Proc sql;
create table work.Mm2 as
Select put(YearNBR,$4.) as YearOfServiceNBR,ReportGroupID,RSOID,
Sum(M_1) as M_1,Sum(M_2) as M_2,Sum(M_3) as M_3,Sum(M_4) as M_4,
Sum(M_5) as M_5,Sum(M_6) as M_6,Sum(M_7) as M_7,Sum(M_8) as M_8,
Sum(M_9) as M_9,Sum(M_10) as M_10,Sum(M_11) as M_11,Sum(M_12) as M_12
From ( Select PlanCD,YearNBR,M_1,M_2,M_3,M_4,M_5,M_6,M_7,M_8,M_9,M_10,M_11,M_12,ReportGroupID,RSOID
From work.Mm1 )
Group By YearNBR,ReportGroupID,RSOID
Order by YearOfServiceNBR,ReportGroupID,RSOID;
quit;
proc sql;
connect to odbc(user=xxxx password=xxxxx dsn=EDWProduction);
create table mapRSO as
select A.*,
case when ReportGroupCD='HHPHO' then 'HHPHO' else ReportGroupCD end as ReportGroupCD,
RSONM,
strip(ReportGroupCD)||'_'||strip(RSONM) as repgrp_RSO,
strip(ReportGroupCD)||'_'||strip(a.RSOID) as repgrp_RSO2
from ( select distinct ReportGroupID,RSOID,
strip(ReportGroupID)||'-'||strip(RSOID) as repgrp
from Mm2 ) A
left
join ( select * from connection to odbc (
select distinct ReportGroupID,ReportGroupCD
from payer.reference.ReportGroup )) B
On a.ReportGroupID=b.ReportGroupID
left
join ( select * from connection to odbc (
select distinct RSOID,RSONM
from payer.reference.RSO
where PlanCD='SECH' )) C
On a.RSOID=c.RSOID;
disconnect from odbc;
quit;

data Mm1 (Drop=M_1-M_12 DateOfServiceDTS ) ;
set Mm2 (keep=YearOfServiceNBR ReportGroupID RSOID M_1 - M_12);
by YearOfServiceNBR ReportGroupID RSOID;
Array mm(12) M_1 - M_12;
do MonthOfServiceNBR=1 to 12;
Mmos=Mm(MonthOfServiceNBR); DateOfServiceDTS=MDY(MonthOfServiceNBR, 01, YearOfServiceNBR); Quarter=Qtr(DateOfServiceDTS);
If "&Pri_Start"d <= DateOfServiceDTS <= "&Pri_End"d or "&Start_dt"d <= DateOfServiceDTS <= "&End_Dt"d then output;
end;
run;

proc sql;
create table Mm as
select A.*,ReportGroupCD,RSONM,repgrp,repgrp_RSO,repgrp_RSO2
from ( select YearOfServiceNBR,ReportGroupID,RSOID,MonthOfServiceNBR,quarter,mmos
from Mm1 ) A
left
join ( select ReportGroupID,RSOID,repgrp,ReportGroupCD,RSONM,
repgrp_RSO,repgrp_RSO2
from MapRSO ) B
on a.ReportGroupID=b.ReportGroupID and a.RSOID=b.RSOID;
quit;

Proc summary data=Mm nway missing;
Var mmos;
Class YearOfServiceNBR ReportGroupID RSOID repgrp_RSO2;
Output out=MM_Sum (drop=_type_ _freq_) sum=;
Run;

proc sql;
/*create table Med3 as*/
create table Med3_pre as /*2016_changes, we need to create a new Med3 table with dummy values included.*/
select A.*,ReportGroupCD,RSONM,repgrp,repgrp_RSO,repgrp_RSO2
from ( select ReportGroupID
, RSOID
, YearOfServiceNBR
, type
, service
, case when YearOfServiceNBR="&cur_yr" then oneday_admits else oneday_admits end as oneday_admits_noibnr
, case when YearOfServiceNBR="&cur_yr" then readmits else readmits end as readmits_noibnr
, case when YearOfServiceNBR="&cur_yr" then admits else admits end as admits_noibnr
, case when YearOfServiceNBR="&cur_yr" then days else days end as days_noibnr
, case when YearOfServiceNBR="&cur_yr" then cost else cost end as cost_noibnr
, case when YearOfServiceNBR="&cur_yr" then oneday_admits*&ibnr else oneday_admits end as oneday_admits
, case when YearOfServiceNBR="&cur_yr" then readmits*&ibnr else readmits end as readmits
, case when YearOfServiceNBR="&cur_yr" then admits*&ibnr else admits end as admits
, case when YearOfServiceNBR="&cur_yr" then days*&ibnr else days end as days
, case when YearOfServiceNBR="&cur_yr" then cost*&ibnr else cost end as cost
from Med2 ) A
left
join ( select ReportGroupID,RSOID,repgrp,ReportGroupCD,RSONM,
repgrp_RSO,repgrp_RSO2
from MapRSO ) B
on a.ReportGroupID=b.ReportGroupID and a.RSOID=b.RSOID;
quit;

/*2016_changes, need to create "dummy" table to handle a ReportGroup that has MemberMonths but
no claims.
Start of "dummy" table code
===============================================================================================
===============================================================================================*/

/*2016_changes, have to create a line for each Type-Service combination*/
proc sql;
create table dummy_type_service_table
(IdVariable num,
Type char(9),
Service char(27));
insert into dummy_type_service_table
values(1,'Acute','Observation Room - Facility')
values(1,'Acute','Inpatient Room & Board')
values(1,'Non-Acute','Rehab Room & Board')
values(1,'Non-Acute','Snf Room & Board')
values(1,'Non-Acute','Psych Inpatient Rm & Bd')
;
quit;

/*2016_changes, have to create a line for both prior and current year*/
proc sql;
create table dummy_table as
(select 1 as IDVariable, ReportGroupID, RSOID, &pri_YR. as YearOfServiceNBR, 0 as oneday_admits_noibnr,
0 as readmits_noibnr, 0 as admits_noibnr, 0 as days_noibnr, 0 as cost_noibnr, 0 as oneday_admits,
0 as readmits, 0 as admits, 0 as days, 0 as cost, ReportGroupCD, RSONM, repgrp,
repgrp_RSO, repgrp_RSO2
from MapRSO)
union all
(select 1 as IDVariable, ReportGroupID, RSOID, &cur_YR. as YearOfServiceNBR, 0 as oneday_admits_noibnr,
0 as readmits_noibnr, 0 as admits_noibnr, 0 as days_noibnr, 0 as cost_noibnr, 0 as oneday_admits,
0 as readmits, 0 as admits, 0 as days, 0 as cost, ReportGroupCD, RSONM, repgrp,
repgrp_RSO, repgrp_RSO2
from MapRSO)
;
quit;

/*2016_changes, combine service-type combinations with prior and current year*/
proc sql;
create table dummy_table2 as
select a.*, b.Type, b.Service
from dummy_table a
left join dummy_type_service_table b
on a.IDVariable=b.IDVariable;
quit;

/*2016_changes, drop the ID variable and change YearOfService to Character*/
data dummy_table3 (drop=IDVariable);
set dummy_table2;
Char_Year = put(YearOfServiceNBR, 4.) ;
drop YearOfServiceNBR ;
rename Char_Year=YearOfServiceNBR ;
run;

/*2016_changes, combine dummy variable with original table*/
data Med3_check_table;
set Med3_pre dummy_table3;
run;

/*2016_changes, need to sum up and group by*/
proc sql;
create table Med3
as select
ReportGroupID, RSOID, YearOfServiceNBR, Type, Service,
sum(oneday_admits_noibnr) as oneday_admits_noibnr,
sum(readmits_noibnr) as readmits_noibnr, sum(admits_noibnr) as admits_noibnr,
sum(days_noibnr) as days_noibnr, sum(cost_noibnr) as cost_noibnr,
sum(oneday_admits) as oneday_admits, sum(readmits) as readmits,
sum(admits) as admits, sum(days) as days, sum(cost) as cost,
ReportGroupCD, RSONM, repgrp, repgrp_RSO, repgrp_RSO2
from Med3_check_table
group by
ReportGroupID, RSOID, YearOfServiceNBR, Type, Service,
ReportGroupCD, RSONM, repgrp, repgrp_RSO, repgrp_RSO2;
quit;

/*
2016_changes,
End of "dummy" table code
===============================================================================================
===============================================================================================*/

*-----------------------------------------------------
Get Raw Admits/Days for Acute & Non-Acute Services
*-----------------------------------------------------;
* Macro to summarize data *;
%Macro Summarize_Data(dsn1,dsn2,dsn3,dsn4,type,var1,var2,var3);
Proc sql;
create table &dsn1 as
select A.*,
mmos,
round( mmos/&no_mos,.01) as avg_mms,
round( oneday_admits/admits, .0001) as oneday_percent,
round( readmits/admits, .0001) as readmits_percent,
round( readmits/mmos)*12000, .01) as readmits_1000,
round( (admits/mmos)*12000, .01) as Adm_1000,
round( (days/mmos)*12000, .01) as Days_1000,
round( calculated days_1000/calculated Adm_1000, .01) as Alos
from ( select YearOfServiceNBR &var1,type,service,
round(sum(oneday_admits)) as oneday_admits,
round(sum(readmits)) as readmits,
round(sum(Admits)) as Admits,
round(sum(Days)) as Days,
round(sum(cost)) as cost
from &dsn2 where upcase(type)=&type
group by YearOfServiceNBR &var1,type,service ) A
left
join ( select YearOfServiceNBR &var1,sum(mmos) as mmos
from &dsn3
group by YearOfServiceNBR &var1 ) B
On a.YearOfServiceNBR=b.YearOfServiceNBR &var2;
quit;

Proc Sort data=&dsn1; By YearOfServiceNBR Type Service;run;

Proc Transpose data=&Dsn1 Out=&Dsn4 name=Measure;
Var oneday_percent readmits_percent readmits_1000 Admits Adm_1000 Days Days_1000 Alos Cost mmos avg_mms;
&var3;
By YearOfServiceNBR Type Service ;
Run;

%Mend Summarize_Data;

%summarize_Data (Measures1,Med3,Mm,M1,'ACUTE',%str(,ReportGroupID,ReportGroupCD,RSOID,repgrp_RSO2),%str(and a.ReportGroupID=b.ReportGroupID and a.RSOID=b.RSOID),%str(Id repgrp_RSO2) );
%summarize_Data (Measures2,Med3,Mm,M2,'NON-ACUTE',%str(,ReportGroupID,ReportGroupCD,RSOID,repgrp_RSO2),%str(and a.ReportGroupID=b.ReportGroupID and a.RSOID=b.RSOID),%str(Id repgrp_RSO2) );
%summarize_Data (Measures3,Med3,Mm,M3,'ACUTE',%str(,ReportGroupID,ReportGroupCD),%str(and a.ReportGroupID=b.ReportGroupID),%str(Id ReportGroupCD) );
%summarize_Data (Measures4,Med3,Mm,M4,'NON-ACUTE',%str(,ReportGroupID,ReportGroupCD),%str(and a.ReportGroupID=b.ReportGroupID),%str(Id ReportGroupCD) );

%summarize_Data (Measures5,Med3,Mm,M5,'ACUTE', , , );
%summarize_Data (Measures6,Med3,Mm,M6,'NON-ACUTE', , , );

*--------------------------------------
Calculate % chg curr vs. prior year
*--------------------------------------;
Data Detail_data1;set M1 M2;run; * summary by GROUP and RSO *;
Proc Sort data=Detail_data1; By Type Service Measure;run;

* Var should list each RSO ;
* 6.2014: BMA is out
06.06.2017 eliminate HHPHO, 07.06.2017, eliminate 102;
Proc Transpose data=Detail_data1 Out=TD1
(rename=(col1=var1)) name=RSO prefix=yr_;
Var /*hhpho_064*/ /*epho_102*/ epho_103
nshs_011 nshs_019 nshs_020 nshs_021
/*nshs_193 *//*07.10.2018*/
nshs_195
nshs_196 /*11.01.2016*/ nshs_198 /*1/1/2020*/
nwpho_415 MRPG_105 SBNRT_189 /*2016_changes, add 195, 415, 105*/
;

id YearOfServiceNBR;
By Type Service measure;
Run;

Data Detail_data2;set M3 M4;run; * Summary by ReportGroupID only ;
Proc Sort data=Detail_data2; By Type Service Measure;run;

* 6.20-14: BMA is out
06.06.2017 eliminate HHPHO;
Proc Transpose data=Detail_data2 Out=TD2
(rename=(col1=var1)) name=RSO prefix=yr_;
Var /*hhpho*/ epho nshs nwpho mrpg sbnrt /*2016_changes, add nwpho and tcma*/ ;
id YearOfServiceNBR;
By Type Service measure;
Run;

Data Detail_data3;set M5 M6;rename col1=z_PHS;run; * zz=pchi - Summary PCHI Network 2015_Program, change z_PCHI to z_PHS ;
Proc Sort data=Detail_data3; By Type Service Measure;run;
Proc Transpose data=Detail_data3 Out=TD3
(rename=(col1=var1)) name=RSO prefix=yr_;
Var z_PHS; /*2015_Program, Change z_PCHI to z_PHS*/
id YearOfServiceNBR;
By Type Service measure;
Run;

* Exclude RSO's that are not subtotaled at the ReportGroupID level;
* 6.2014: BMA is out. 07.06.2017, ADD epho below;
Data TD; /* TD transposed Data */
set TD1 TD2 TD3;
if missing(yr_&pri_yr) then chg =. ; /* 6.7.10 */
else if yr_&pri_yr = 0 then chg =. ; /* 6.7.10 */
else chg=round( yr_&cur_yr/yr_&pri_yr-1, .001);
where RSO not in ('NWPHO','MRPG','EPHO','SBNRT'); /* 6.7.10 */ /* 5.2011 - Added HHS & PMG *//*2016_changes, added NWPHO and TCMA. 06.06.2017 eliminate hhpho*/
/* 6.2012 - Removed CHA */
/*2015_Program. Removed CAMC and HHPHO. Does HMA also need to be removed???? Looks like oversight from 2014?
2015_Program Removing HMA*/
run;

Proc Sort data=TD; By Type Service Measure RSO;run;
Proc Transpose data=TD Out=chg (drop=_name_);
Var chg;
id RSO;
By Type Service measure;
Run;

Proc Sort data=Detail_data1; By YearOfServiceNBR Type Service Measure;run;

* Var should list each RSO ;
* 6.2014: BMA is out
06.06.2017 eliminate HHPHO. 07.06.2017 eliminate 102;
Proc Transpose data=Detail_data1 Out=outData1
(rename=(col1=var1)) name=RSO;
Var /*hhpho_064*/ /*epho_102*/ epho_103
nshs_011 nshs_019 nshs_020 nshs_021
/*nshs_193 *//*07.10.2018*/
nshs_195
nshs_196 /*11.01.2016*/ nshs_198 /*1/1/20*/
nwpho_415 mrpg_105 SBNRT_189 /*2016_changes, add 195, 415, 105*/

;
By YearOfServiceNBR Type Service measure;
Run;
Proc Sort data=Detail_data2; By YearOfServiceNBR Type Service Measure;run;

* Include ReportGroupIDs for which we show subtotals 07.06.2017 eliminate epho from subtotals;
Proc Transpose data=Detail_data2 Out=outData2
(rename=(col1=var1)) name=RSO;
Var /*epho*/ nshs;
/*2015_Program remove crma*/
By YearOfServiceNBR Type Service measure;
Run;
Proc Sort data=Detail_data3; By YearOfServiceNBR Type Service Measure;run;
Proc Transpose data=Detail_data3 Out=outData3
(rename=(col1=var1)) name=RSO;
Var z_Phs; /*2015_Program Change z_Pchi to z_Phs*/
By YearOfServiceNBR Type Service measure;
Run;

Proc sql;
create table outData4 as select * from (
select strip('YTD'||' '||YearOfServiceNBR) as YearOfServiceNBR,type,service,measure,RSO,var1
from OutData1 /* ReportGroupID and RSO */
union all
select strip('YTD'||' '||YearOfServiceNBR) as YearOfServiceNBR,type,service,measure,RSO,var1
from OutData2 /* ReportGroupID */
union all
select strip('YTD'||' '||YearOfServiceNBR) as YearOfServiceNBR,type,service,measure,RSO,var1
from OutData3 /* PCHI */
union all
select '% chg' as YearOfServiceNBR,type,service,measure,RSO,chg as var1
from TD);
quit;

*------------------------------------------------------------------
Flag Data appropriately THP-MP summary and detail report.
*------------------------------------------------------------------;
Data OutData5;
set outData4;
length measure2 $20.;
if type='Acute' and service='Inpatient Room & Board' and measure='mmos'
then do;service='Total Member Months'; measure=' '; rank1=1; measure2="Total Member Months";rank3=1;end;
if type='Acute' and service='Inpatient Room & Board' and measure='avg_mms'
then do;service='Avg Member Months'; measure=' '; rank1=2; measure2="Avg Member Months";rank3=2;end;
if type='Acute' and service='Inpatient Room & Board' and measure='Admits' then do;rank1=3;measure2="IP Admits";rank3=3;end;
if type='Acute' and service='Inpatient Room & Board' and measure='Adm_1000' then do;rank1=4;measure2="IP Admits/1000";rank3=4;end;
if type='Acute' and service='Inpatient Room & Board' and measure='Days' then do;rank1=5;measure2="Days";rank3=5;end;
if type='Acute' and service='Inpatient Room & Board' and measure='Days_1000' then do;rank1=6;measure2="Days/1000";rank3=6;end;
if type='Acute' and service='Inpatient Room & Board' and measure='readmits_1000' then do;rank1=7;measure2="readmits/1000";rank3=7;end;
if type='Acute' and service='Observation Room - Facility' and measure='Admits' then do;rank1=8;measure2="Obs (Med/Surg)";rank3=12;end;
if type='Acute' and service='Observation Room - Facility' and measure='Adm_1000' then do;rank1=9;measure2="Obs/1000 (Med/Surg)";rank3=13;end;
if type='Acute' and service='Inpatient Room & Board' and measure='Alos' then do;rank1=10;measure2="ALOS";rank3=14;end;
if type='Acute' and service='Inpatient Room & Board' and measure='readmits_percent' then do;rank1=11;measure2="% Readmit";rank3=15;end;
if type='Acute' and service='Inpatient Room & Board' and measure='oneday_percent' then do;rank1=12;measure2="% 1-Day";rank3=16;end;
if type='Non-Acute' and service='Snf Room & Board' and measure='Admits' then do;rank1=13;measure2="SNF Admits";rank3=8;end;
if type='Non-Acute' and service='Snf Room & Board' and measure='Adm_1000' then do;rank1=14;measure2="SNF Admits/1000";rank3=9;end;
if type='Non-Acute' and service='Snf Room & Board' and measure='Days' then do;rank1=15;end;
if type='Non-Acute' and service='Snf Room & Board' and measure='Days_1000' then do;rank1=16;end;
if type='Non-Acute' and service='Rehab Room & Board' and measure='Admits' then do;rank1=17;measure2="Rehab Admits";rank3=9;end;
if type='Non-Acute' and service='Rehab Room & Board' and measure='Adm_1000' then do;rank1=18;measure2="Rehab Admits/1000";rank3=10;end;
if type='Non-Acute' and service='Rehab Room & Board' and measure='Days' then do;rank1=19;end;
if type='Non-Acute' and service='Rehab Room & Board' and measure='Days_1000' then do;rank1=20;end;
if type='Non-Acute' and service='Psych Inpatient Rm & Bd' and measure='Admits' then do;rank1=21;end;
if type='Non-Acute' and service='Psych Inpatient Rm & Bd' and measure='Adm_1000' then do;rank1=22;end;
if type='Non-Acute' and service='Psych Inpatient Rm & Bd' and measure='Days' then do;rank1=23;end;
if type='Non-Acute' and service='Psych Inpatient Rm & Bd' and measure='Days_1000' then do;rank1=24;end;
if type='not yet' and measure='NMG Balance before PIP (pmpm)' then do;rank1=25;measure2="NMG Balance before PIP (pmpm)";rank3=17;end;
if type='not yet' and measure='NMG Balance before PIP (pmpm)' then do;rank1=26;measure2="NMG Balance after PIP (pmpm)";rank3=18;end;

if YearOfServiceNBR="YTD &cur_yr." then rank2=1;
if YearOfServiceNBR="YTD &pri_yr." then rank2=2;
if YearOfServiceNBR='% chg' then rank2=3;

*if RSO='BMA_010' then rank4=1; /* 6.2014 */
*if RSO='CAMC_180' then rank4=2; /* 2015_Program */
*if RSO='CHA_028' then rank4=3; /* 6.2012 */
*if RSO='CMPC_031' then rank4=4; /* 6.2012 */
*if RSO='CMPC_032' then rank4=5; /* 6.2012 */
*if RSO='CMPC' then rank4=6; /* 6.2012 */
*if RSO='CRMA_116' then rank4=7; /* 2015_Program */
*if RSO='CRMA_117' then rank4=8; /* 2015_Program */
*if RSO='CRMA_118' then rank4=9; /* 2015_Program */
*if RSO='CRMA' then rank4=10; /* 2015_Program */
/*if RSO='EPHO_102' then rank4=11;*/ /* 07.06.2017, eliminate 102*/
if RSO='EPHO_103' then rank4=12;
/*if RSO='EPHO' then rank4=13;*/ /* 07.06.2017, eliminate 102*/
/*if RSO='HHPHO_064' then rank4=14;*/ /*06.06.2017*/
*if RSO='HHS_065' then rank4=15; /* 5.2011 */
*if RSO='HHS' then rank4=16;
/*if RSO='HMA_221' then rank4=17;*/
if RSO='NSHS_011' then rank4=15;
if RSO='NSHS_019' then rank4=16;
if RSO='NSHS_020' then rank4=17;
if RSO='NSHS_021' then rank4=18;
/*if RSO='NSHS_193' then rank4=19;*//*07.10.2018*/
if RSO='NSHS_195' then rank4=20;
if RSO='NSHS_198' then rank4=22; /*2020_changes, added NSHS_198*/
if RSO='NSHS_196' then rank4=21; /*11.01.2016*/
if RSO='NSHS' then rank4=23;
if RSO='NWPHO_415' then rank4=24; /*2016_changes, added NWPHO_415*/
if RSO='MRPG_105' then rank4=25; /*2016_changes, added TCMA_105*/
if RSO='SBNRT_189' then rank4=26;
*if RSO='PMG_033' then rank4=24; /* 5.2011 Add PMG. Change rank for Prima & PCHI */ /* 2015_Program Exclude PMG 033 */
/*if RSO='Prima_009' then rank4=25; /* 6.7.10 Add PrimaCARE */
if RSO='z_PHS' then rank4=27; /* 6.7.10 Change PCHI rank from 24 to 25 *//* 2015_Program Change z_PCHI to z_PHS */
run;

proc format;
value $RSOfmt /*'BMA_010' ='BMA'*/ /* 6.2014 */
/* 'CHA_028' ='CHA' */ /* 6.2012 */
/* 'CMPC_031' = 'Compass-ABB' */ /* 6.2012 */
/* 'CMPC_032' = 'Compass-EBW' */ /* 6.2012 */
/* 'CAMC_180'='CAMC'*/ /* 2015_Program */
/* 'HHPHO_064' ='HHPHO' */ /* 06.06.2017 */
/* 'HHS_065' ='HHS-A' */ /* 5.2011 */
/* 'HMA_221' ='HMA'*/
/* 'EPHO_102'='EPHO-CIM'*/ /* 07.06.2017, eliminate 102*/
'EPHO_103'='EPHO' /* 07.06.2017, change epho-blma to just epho*/
/* 'CRMA_116'='CRMA-FR'*/ /* 2015_Program */
/* 'CRMA_117'='CRMA-NA'*/ /* 2015_Program */
/* 'CRMA_118'='CRMA-MO'*/ /* 2015_Program */
'NSHS_011'='NS-HMG'
'NSHS_019'='NS-Puritan'
'NSHS_020'='NS-Peabody'
'NSHS_021'='NS-NSMG'
/* 'NSHS_193'='NS-GL'*//*07.10.2018*/
'NSHS_195'='NS-Family' /*2016_changes, adding label for Family Doctors group*/
'NSHS_196'='NS-Rowley' /*11.01.2016*/
/* 'PMG_033'='PMG' /* 5.2011 Add PMG *//* 2015_Program Exclude PMG 033*/
/* 'Prima_009'='PRIMA' 6.7.10 Add PrimaCARE */
'NSHS_198'='NS-Ipswich' /*1/1/20*/
'NWPHO_415'='NWPHO' /*2016_changes, adding label for NWPHO group*/
'MRPG_105'='MRPG'
'SBNRT_189'='SBNRT'
/*2016_changes, adding label for TCMA group*/
'z_PHS' ='PHS';

value $Measfmt 'Admits'='Admits'
'Adm_1000' ='Admits/1000'
'Days' ='Days'
'Days_1000' ='Days/1000'
'Alos' ='ALOS'
'oneday_percent'='% 1-Day'
'readmits_percent'='% Readmit'
'readmits/1000' ='readmits/1000';
run;

proc sort data=OutData5 out=summary_file;
By rank3 rank4 YearOfServiceNBR Type Service measure RSO;
where rank3 ne .;
run; * Summary File *;

proc sort data=OutData5 out=Detail_File;
By rank2 rank1 rank4 YearOfServiceNBR Type Service measure RSO;
where rank1 ne .;
run; * Detail File *;

*ods path tmplmst(update) sashelp.tmplmst(read); /* 11.2011 */
*%include "&ExcelXP_location.\ExcelXP.sas"; /* 11.2011 */
ods listing close;

ods tagsets.ExcelXP path ="&report_location." file = "TMP_IP_Dashboard_&Cur_Yr.&filename..xls" style=sansPrinter;

ods tagsets.ExcelXP options(embedded_titles='yes' embedded_footnotes='yes' sheet_name = 'Summary' Orientation='Landscape' FitToPage='yes' autofit_height='yes');

title1 j=left font='Arial Narrow' bold height=14pt "THP-MP &cur_yr. Dashboard";
title2 j=left font='Arial Narrow' bold height=14pt "Report date: %sysfunc(today(), monname.) 10, %sysfunc(today(), year4.)";;
title3 ;
title4 j=left font='Arial Narrow' height=10pt "YTD &cur_yr. data: Incurred &start_dt.-&End_dt., Paid through &Pdapp_dt. - IBNR: &ibnr.";
title6 ;

footnote1 j=left font='Times New Roman' height=10pt bold 'Notes:';
footnote2 j=left font='Times New Roman' height=10pt 'Readmit Defined as an Acute admission occurring 30 days or less from an Acute admission (transfers are not considered).';
*footnote3 j=left font='Times New Roman' height=10pt 'Observation Defined as an Acute observation that did not result in an admission.';

footnote4;
footnote5 j=left font='Times New Roman' height=10pt 'Clinical Analytics - Confidential';

proc report nocenter nowindows data= Summary_File missing
style(report)=[font=(Times, 10pt) cellspacing=1 BORDERWIDTH=.1 bordercolor=black rules=rows frame=hsides]
style(header)=[background=white font=(Times, 10pt, bold) BORDERWIDTH=.1 bordercolor=black]
style(column)=[font=(Times, 10pt)];
where YearOfServiceNBR="YTD &cur_yr";
column RSO measure2,var1;
define RSO / group order=data ' ' format=$RSOfmt. style(column)=[font_weight=bold cellwidth=2.0in];
define measure2 / group across order=data ' ' style(column)=[font_weight=bold cellwidth=3in];
*define var1 / group order=data ' ';
define var1 / group order=data ' ' style={tagattr='format:#,###.00'}; /* 8.2010 */

run;

ods tagsets.ExcelXP options(embedded_titles='yes' embedded_footnotes='yes' sheet_name = 'Detail' Orientation='Landscape' FitToPage='yes' autofit_height='yes');

title1 j=left font='Arial Narrow' bold height=14pt "THP-MP &cur_yr. Dashboard";
title2 j=left font='Arial Narrow' bold height=14pt "Report date: %sysfunc(today(), monname.) 10, %sysfunc(today(), year4.)";;
title3 ;
title4 j=left font='Arial Narrow' height=10pt "YTD &cur_yr. data: Incurred &start_dt.-&End_dt., Paid through &Pdapp_dt. - IBNR: &ibnr.";
title5 j=left font='Arial Narrow' height=10pt "YTD &pri_yr. data: Incurred &pri_Start.-&pri_End., Paid through &Pdapp_dt.";
title6 ;

footnote1 j=left font='Times New Roman' height=10pt bold 'Notes:';
footnote2 j=left font='Times New Roman' height=10pt 'Readmit Defined as an Acute admission occurring 30 days or less from an Acute admission (transfers are not considered).';
*footnote3 j=left font='Times New Roman' height=10pt 'Observation Defined as an Acute observation that did not result in an admission.';

footnote4;
footnote5 j=left font='Times New Roman' height=10pt 'Clinical Analytics - Confidential';
proc report nocenter nowindows data= Detail_File missing
style(report)=[font=(Times, 10pt) cellspacing=1 BORDERWIDTH=.1 bordercolor=black rules=rows frame=hsides]
style(header)=[background=white font=(Times, 10pt, bold) BORDERWIDTH=.1 bordercolor=black]
style(column)=[font=(Times, 10pt)];
column YearOfServiceNBR service measure RSO,var1;
define YearOfServiceNBR / group order=data ' ' order=data style(column)=[font_weight=bold cellwidth=0.5in];
define service / group order=data ' ' style(column)=[font_weight=bold cellwidth=1.0in];
define measure / group order=data ' ' format=$Measfmt. style(column)=[font_weight=bold cellwidth=2.0in];
define RSO / group across order=data ' ' format=$RSOfmt. style(column)=[font_weight=bold cellwidth=3in];
*define var1 / group order=data ' ';
define var1 / group order=data ' ' style={tagattr='format:#,###.00'}; /* 8.2010 */

compute after YearOfServiceNBR;
line '';
endcomp;

run;

ods tagsets.ExcelXP close;
ods listing;
run;

/* Export the detail to as an excel file */
/*proc export data=dataset.tmp&start_dt._&end_dt._&Pdapp_dt.*/
/* outfile="&report_location.\tmp_&start_dt._&end_dt._&Pdapp_dt.&filename..xls"*/
/* DBMS=EXCELCS REPLACE;*/
/* sheet=tmp;*/
/* server=%unquote(%bquote(&_CLIENTMACHINE));*/
/* port=8621;*/
/*run; */

proc export
data=dataset.tmp&start_dt._&end_dt._&Pdapp_dt.
dbms=xls
outfile="&report_location.\tmp_&start_dt._&end_dt._&Pdapp_dt.&filename..xls"
;
run;

[Amir]

Hi @ljw4 ,

Welcome to the SAS Communities forum. Thank you for sharing your code, we don't normally see so much code in one post, so only the relevant parts are likely to be enough. Please note that there is an icon to "Insert SAS Code" when posting SAS code to the forum.

Please confirm what your actual question is?

Are you just seeking confirmation that the syntax is correct? (Try running it.)

Or are you wanting to know "will it work"? (Depends on what you want to do which you will need to explain.)

Or you are getting an error when running it? (Please share the log with the incorrect step and error message.)

Or another question (please specify clearly what it is.)

Kind regards,

Amir.

[ljw4]

Sorry about that.

I am getting an error when running this part:

%Macro Summarize_Data(dsn1,dsn2,dsn3,dsn4,type,var1,var2,var3);
Proc sql;
create table &dsn1 as
select A.*,
mmos,
round( mmos/&no_mos,.01) as avg_mms,
round( oneday_admits/admits, .0001) as oneday_percent,
round( readmits/mmos)*12000, .01) as readmits_1000,
round( readmits/admits, .0001) as readmits_percent,
round( (admits/mmos)*12000, .01) as Adm_1000,
round( (days/mmos)*12000, .01) as Days_1000,
round( calculated days_1000/calculated Adm_1000, .01) as Alos
from ( select YearOfServiceNBR &var1,type,service,
round(sum(oneday_admits)) as oneday_admits,
round(sum(readmits)) as readmits,
round(sum(Admits)) as Admits,
round(sum(Days)) as Days,
round(sum(cost)) as cost
from &dsn2 where upcase(type)=&type
group by YearOfServiceNBR &var1,type,service ) A
left
join ( select YearOfServiceNBR &var1,sum(mmos) as mmos
from &dsn3
group by YearOfServiceNBR &var1 ) B
On a.YearOfServiceNBR=b.YearOfServiceNBR &var2;
quit;

Proc Sort data=&dsn1; By YearOfServiceNBR Type Service;run;

Proc Transpose data=&Dsn1 Out=&Dsn4 name=Measure;
Var oneday_percent readmits_percent readmits_1000 Admits Adm_1000 Days Days_1000 Alos Cost mmos avg_mms;
&var3;
By YearOfServiceNBR Type Service ;
Run;

%Mend Summarize_Data;

%summarize_Data (Measures1,Med3,Mm,M1,'ACUTE',%str(,ReportGroupID,ReportGroupCD,RSOID,repgrp_RSO2),%str(and a.ReportGroupID=b.ReportGroupID and a.RSOID=b.RSOID),%str(Id repgrp_RSO2) );
%summarize_Data (Measures2,Med3,Mm,M2,'NON-ACUTE',%str(,ReportGroupID,ReportGroupCD,RSOID,repgrp_RSO2),%str(and a.ReportGroupID=b.ReportGroupID and a.RSOID=b.RSOID),%str(Id repgrp_RSO2) );
%summarize_Data (Measures3,Med3,Mm,M3,'ACUTE',%str(,ReportGroupID,ReportGroupCD),%str(and a.ReportGroupID=b.ReportGroupID),%str(Id ReportGroupCD) );
%summarize_Data (Measures4,Med3,Mm,M4,'NON-ACUTE',%str(,ReportGroupID,ReportGroupCD),%str(and a.ReportGroupID=b.ReportGroupID),%str(Id ReportGroupCD) );

%summarize_Data (Measures5,Med3,Mm,M5,'ACUTE', , , );
%summarize_Data (Measures6,Med3,Mm,M6,'NON-ACUTE', , , );

*--------------------------------------
Calculate % chg curr vs. prior year
*--------------------------------------;
Data Detail_data1;set M1 M2;run; * summary by GROUP and RSO *;
Proc Sort data=Detail_data1; By Type Service Measure;run;

This is the log:

1 The SAS System 13:07 Monday, October 26, 2020

1 ;*'
_
49
1 ! ;*";*/;quit;run;
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='2020 TMP Dashboard_TESTforREADMITS'
_______________________________
49
3 ! ;
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTNAME='';
7 %LET _SASPROGRAMFILE=;
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=PNG;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
22
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
__
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.

23 GOPTIONS ACCESSIBLE;
24 %Mend Summarize_Data;
25
26
27 %summarize_Data (Measures1,Med3,Mm,M1,'ACUTE',%str(,ReportGroupID,ReportGroupCD,RSOID,repgrp_RSO2),%str(and
27 ! a.ReportGroupID=b.ReportGroupID and a.RSOID=b.RSOID),%str(Id repgrp_RSO2) );
28 %summarize_Data (Measures2,Med3,Mm,M2,'NON-ACUTE',%str(,ReportGroupID,ReportGroupCD,RSOID,repgrp_RSO2),%str(and
________________________________________________________________
49
28 ! a.ReportGroupID=b.ReportGroupID and a.RSOID=b.RSOID),%str(Id repgrp_RSO2) );
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
29 %summarize_Data (Measures3,Med3,Mm,M3,'ACUTE',%str(,ReportGroupID,ReportGroupCD),%str(and
29 ! a.ReportGroupID=b.ReportGroupID),%str(Id ReportGroupCD) );
30 %summarize_Data (Measures4,Med3,Mm,M4,'NON-ACUTE',%str(,ReportGroupID,ReportGroupCD),%str(and
______________________________________________
49
30 ! a.ReportGroupID=b.ReportGroupID),%str(Id ReportGroupCD) );
2 The SAS System 13:07 Monday, October 26, 2020

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
31
32 %summarize_Data (Measures5,Med3,Mm,M5,'ACUTE', , , );
33 %summarize_Data (Measures6,Med3,Mm,M6,'NON-ACUTE', , , );
34
35 *--------------------------------------
36 Calculate % chg curr vs. prior year
37 *--------------------------------------;
38 Data Detail_data1;set M1 M2;run; * summary by GROUP and RSO *;
39 Proc Sort data=Detail_data1; By Type Service Measure;run;
40
41
42 GOPTIONS NOACCESSIBLE;
NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
43 %LET _CLIENTTASKLABEL=;
44 %LET _CLIENTPROCESSFLOWNAME=;
45 %LET _CLIENTPROJECTPATH=;
46 %LET _CLIENTPROJECTNAME=;
47 %LET _SASPROGRAMFILE=;
48
49 ;*';*";*/;quit;run;
50 ODS _ALL_ CLOSE;
51
52
53 QUIT; RUN;
54

[ballardw]

When debugging macro code you need to set OPTIONS MPRINT; before execution so you can see all the statements actually created by your macro. The error or warning messages will also appear more in context.

If the problem seems to be with macro logic use the MLOGIC option and if the value of macro variables you may want SYMBOLGEN to show how the variables are constructed.

Turn the options off using Options Nomprint ; (Nomlogic Nosymbolgen if needed).

1 The SAS System 13:07 Monday, October 26, 2020

1 ;*'
_
49
1 ! ;*";*/;quit;run;
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.

Is not an error. What it means that somewhere you have a quote followed by a character.

SAS uses constructs like '01JAN2020'd , note the d immediately after the quote, to denote a date literal value. Other similar constructs are used for time, datetime or name literals. The note is telling that some time in future the construct you build with quote/character combination may become one of those reserved meanings.

I suspect you might be getting this because you have quoted values as macro parameters and when the macro variable value like 'ACUTE' gets resolved the following text is triggering the NOTE.

It is often a better idea to pass the value and supply the quotes in the body of the code such as where &type is ACUTE and not "ACUTE".

where upcase(type)= "&type."

This approach would allow use of the value of type in other statements, like Titles, without causing issues with the quotes.

[ljw4]

Ok thank you. I will try this.