SAS EG 4.2 Hangs when creating ODS PDF Macro with Large output

[rch1292]

Hi,

I have a dataset with distinct Grps and Specialty ( thousands of records )

If I have (say) 20, the process works fine, however, when doing large number - SAS Hangs at the end of the processing.

How can I correct this ?

%LET xExport = /im/data/users/rch1292/BP/Reports/;
%LET xLogo   = /im/data/users/rch1292/BP/Logo/;

/************************************/
/* Create Group Summary Temp Table  */
/*   Exclude Providers with No Data */
/************************************/
PROC SQL;
CREATE TABLE xOut.GROUP_SUMMARY AS (
SELECT DISTINCT
    xGRP.ST_CD,
    xGRP.ST_CATEGORY_CD,
    xGRP.GRP_NM,
    xGRP.GRP_ID,
    xGRP.SPECIALTY,

    xPhy.PROV_ID,
    xPhy.PROV_NM,

/*******************/
/* Provider Detail */
/*******************/
    PhyNorm.VOL_PHY_CNT     AS VOL_PHY_CNT,
    PhyNorm.TOT_PHY_CST     AS TOT_PHY_CST,
    PhyNorm.EXP_PHY_TOT     AS EXP_PHY_TOT,

/*************************************/
/* Ratio for Weighted Mean ETG Index */
/*************************************/
    PhyFlg.T_ETG30_OE_RATIO AS PHY_OE_MGP_RATIO,

/***************************/
/* Computed Column         */
/* Weighted Mean ETG Index */
/***************************/
    GrpFlg.OE_MGP_WTD       AS GRP_OE_MGP_WTD,

/***************************/
/* 90% Confidence Interval */
/*  Lower - Upper          */
/***************************/
    GrpFlg.LCL_MGP_CST      AS LCL_MGP_CST,
    GrpFlg.UCL_MGP_CST      AS UCL_MGP_CST,

/*******************/
/* Cost Compliance */
/*******************/
    GrpFlg.FLAG30_OE_RATIO  AS COST_RATIO

FROM
    xOut.GROUP_ID xGRP

    INNER JOIN
    xData.GRP_OE_FLAGS GrpFlg
                        ON xGRP.GRP_ID          = GrpFlg.grp_id
                       AND xGRP.SPECIALTY       = GrpFlg.SPECIALTY
                       AND xGRP.ST_CATEGORY_CD  = GrpFlg.ST_CATEGORY_CD
                       AND xGRP.ST_CD           = GrpFlg.ST_CD

    INNER JOIN
    xOut.PROV_ID xPhy
                        ON xGRP.GRP_ID         = xPhy.GRP_ID
                       AND xGRP.SPECIALTY      = xPhy.SPECIALTY
                       AND xGRP.ST_CATEGORY_CD = xPhy.ST_CATEGORY_CD
                       AND xGRP.ST_CD          = xPhy.ST_CD

    INNER JOIN
    xData.PHY_OE_FLAGS PhyFlg
                        ON xPhy.GRP_ID         = PhyFlg.GRP_ID
                       AND xPhy.SPECIALTY      = PhyFlg.SPECIALTY
                       AND xPhy.ST_CATEGORY_CD = PhyFlg.ST_CATEGORY_CD
                       AND xPhy.ST_CD          = PhyFlg.ST_CD
                       AND xPhy.PROV_ID        = PhyFlg.REPONS_PRVID1

    INNER JOIN
    xData.NORMS_PHY PhyNorm
                        ON xPhy.GRP_ID         = PhyNorm.GRP_ID
                       AND xPhy.SPECIALTY      = PhyNorm.SPC_DSC
                       AND xPhy.ST_CATEGORY_CD = PhyNorm.ST_CATEGORY_CD
                       AND xPhy.ST_CD          = PhyNorm.ST_CD
                       AND xPhy.PROV_ID        = PhyNorm.PROV_ID

    INNER JOIN
    xData.NORMS_ETG_PHY PhyNormETG
                        ON xPhy.GRP_ID         = PhyNormETG.GRP_ID
                       AND xPhy.SPECIALTY      = PhyNormETG.SPC_DSC
                       AND xPhy.ST_CATEGORY_CD = PhyNormETG.ST_CATEGORY_CD
                       AND xPhy.ST_CD          = PhyNormETG.ST_CD
                       AND xPhy.PROV_ID        = PhyNormETG.PROV_ID

);
QUIT;

PROC SORT
  DATA = xOut.GROUP_SUMMARY;
  BY ST_CD
     ST_CATEGORY_CD
     GRP_ID
     SPECIALTY
     GRP_NM
     PROV_ID
     PROV_NM;
RUN;

%MACRO GRP_SMRY_DataSet(xST_CD, xST_CAT_CD, xGRP_ID, xSPCLTY);

/************************************/
/* Create Detail Data               */
/************************************/
DATA WORK.GRP_SMRY_DETAIL_DATA;
SET  xOut.GROUP_SUMMARY;
WHERE
    ST_CD          = "&xST_CD."
AND ST_CATEGORY_CD = "&xST_CAT_CD."
AND SPECIALTY      = "&xSPCLTY."
AND GRP_ID         = "&xGRP_ID.";
RUN;

DATA WORK.GRP_SMRY_DETAIL_DATA;
SET  WORK.GRP_SMRY_DETAIL_DATA;
/************************************/
/* Group Summary Detail             */
/************************************/
       Physician_Id              = PROV_ID;
       Physician_Name            = PROV_NM;
       Episode_Vol               = VOL_PHY_CNT;
       Ttl_Dlrs_Allwd            = TOT_PHY_CST;
       Expctd_Dlrs_Allwd         = EXP_PHY_TOT;
       Prfrmnc_Ratio             = PHY_OE_MGP_RATIO;

  KEEP Physician_Id
       Physician_Name
       Episode_Vol
       Ttl_Dlrs_Allwd
       Expctd_Dlrs_Allwd
       Prfrmnc_Ratio;
RUN;

/************************************/
/* Create Weighted Data             */
/* and Cost Compliance              */
/************************************/
DATA WORK.GRP_SMRY_WEIGHT_DATA;
SET  xOut.GROUP_SUMMARY;
WHERE
    ST_CD          = "&xST_CD."
AND ST_CATEGORY_CD = "&xST_CAT_CD."
AND SPECIALTY      = "&xSPCLTY."
AND GRP_ID         = "&xGRP_ID.";

DATA WORK.GRP_SMRY_WEIGHT_DATA;
SET  WORK.GRP_SMRY_WEIGHT_DATA;
/************************************/
/* Group Summary Weight             */
/************************************/
       Weighted_Mean_ETG_Index = GRP_OE_MGP_WTD;

/************************************/
/* 90% Confidence Interval          */
/*  Lower - Upper                   */
/************************************/
  IF LCL_MGP_CST = . THEN LCL_MGP_CST = 0;
  IF UCL_MGP_CST = . THEN UCL_MGP_CST = 0;
       Lower_CnfdncIntrvl_90prcnt = LCL_MGP_CST;
       Upper_CnfdncIntrvl_90prcnt = UCL_MGP_CST;

/************************************/
/* Cost Compliance                  */
/************************************/
       Cost_Compliance = COST_RATIO;

  KEEP Weighted_Mean_ETG_Index
       Lower_CnfdncIntrvl_90prcnt
       Upper_CnfdncIntrvl_90prcnt
       Cost_Compliance;
RUN;

%MEND GRP_SMRY_DataSet;

%MACRO GRP_SMRY_Rprt(xST_CD, xST_CAT_CD, xGRP_ID, xGRP_NM, xSPCLTY);

/************************************/
/* Define Report Type               */
/************************************/
%LET xRpt     = GROUP SUMMARY;

/************************************/
/* Define OPTIONS    
GOPTIONS DEVICE = ACTXIMG;          */
/************************************/

ODS LISTING CLOSE;
OPTIONS NODATE NONUMBER ORIENTATION=LANDSCAPE;

/************************************/
/* Define Output and Report Style   */
/************************************/
ODS PDF FILE = "&xExport.GS_&xST_CD._&xST_CAT_CD._&xSPCLTY._&xGRP_ID..pdf" STYLE = JOURNAL NOTOC;

/************************************/
/* Keep on same Page                */
/************************************/
ODS PDF STARTPAGE = YES;

/************************************/
/* Used for PDF and should be       */
/*    something distinct that would */
/*    not be in existing data.      */
/* A unique character               */
/************************************/
ODS ESCAPECHAR='~';

/************************************/
/* Logo for Report                  */
/************************************/
TITLE1
  JUSTIFY = LEFT   '~S={PREIMAGE="&xLogo.&STATE._Logo.png"}';

/************************************/
/* Report Heading                   */
/************************************/
TITLE2
  JUSTIFY = CENTER "&xRpt."
  JUSTIFY = LEFT   ' '
  JUSTIFY = CENTER "&xGRP_ID."
  JUSTIFY = CENTER "&xGRP_NM."
  JUSTIFY = CENTER "&xSPCLTY."
  JUSTIFY = CENTER 'Run Date: ' &CLM_PRD_STRT_DT. ' - ' &CLM_PRD_END_DT.;

/************************************/
/* Output Detail Data               */
/************************************/
PROC REPORT NOWD
  DATA = WORK.GRP_SMRY_DETAIL_DATA;

  COLUMN Physician_Id
         Physician_Name
         Episode_Vol
         Ttl_Dlrs_Allwd
         Expctd_Dlrs_Allwd
         Allwd_Dlrs_per_Epsd
         Expctd_Alwd_Dlrs_per_Epsd
         Prfrmnc_Ratio
         Prfrmnc_Ratio_Cmptd;

  DEFINE Physician_Id              / 'Physician ID';
  DEFINE Physician_Name            / 'Physician Name';
  DEFINE Episode_Vol               / FORMAT=COMMA10.   ANALYSIS  SUM 'Episode Vol'            ;
  DEFINE Ttl_Dlrs_Allwd            / FORMAT=DOLLAR15.  ANALYSIS  SUM 'Total    $ Allowed'     ;
  DEFINE Expctd_Dlrs_Allwd         / FORMAT=DOLLAR15.  ANALYSIS  SUM 'Expected $ Allowed'     ;
  DEFINE Allwd_Dlrs_per_Epsd       / FORMAT=DOLLAR15.  COMPUTED      'Allowed  $ per Episode' ;
  DEFINE Expctd_Alwd_Dlrs_per_Epsd / FORMAT=DOLLAR15.  COMPUTED      'Expected $ per Episode' ;
  DEFINE Prfrmnc_Ratio             / NOPRINT ;
  DEFINE Prfrmnc_Ratio_Cmptd       / FORMAT=8.2        COMPUTED      'Performence Ratio'      ;

/******************************************/
/* Calculate by Column Position           */
/* Allwd_Dlrs_per_Epsd is a NEW VAR       */
/******************************************/
/* Compute by Col:                        */
/* Ttl_Dlrs_Allwd / Episode_Vol           */
/******************************************/
  COMPUTE Allwd_Dlrs_per_Epsd;
      Allwd_Dlrs_per_Epsd = Ttl_Dlrs_Allwd.SUM / Episode_Vol.SUM;
  ENDCOMP;

/******************************************/
/* Calculate by Column Position           */
/* Expctd_Alwd_Dlrs_per_Epsd is a NEW VAR */
/******************************************/
/* Compute by Col:                        */
/* Expctd_Dlrs_Allwd / Episode_Vol        */
/******************************************/
  COMPUTE Expctd_Alwd_Dlrs_per_Epsd;
      Expctd_Alwd_Dlrs_per_Epsd = Expctd_Dlrs_Allwd.SUM / Episode_Vol.SUM;
  ENDCOMP;

/******************************************/
/* Calculate by Column Position           */
/* Prfrmnc_Ratio_Cmptd is a NEW VAR       */
/******************************************/
/* Compute by Col:                        */
/* Prfrmnc_Ratio  / Episode_Vol           */
/******************************************/
  COMPUTE Prfrmnc_Ratio_Cmptd;
      Prfrmnc_Ratio_Cmptd = Prfrmnc_Ratio.SUM / Episode_Vol.SUM;
  ENDCOMP;

/************************************/
/* After Detail; Summarize Columns  */
/*   based on ANALYSIS & COMPUTED   */
/************************************/
  RBREAK AFTER /SUMMARIZE;
RUN;

ODS PDF STARTPAGE = NO;
/************************************/
/* Create Blank Lines               */
/************************************/
ods text=' ';
ods text=' ';
ods text=' ';

/*****************************************/
/* This prints right beneath PROC REPORT */
/* on the same page due to:              */
/* ODS PDF STARTPAGE = NO  IF ROOM AVLBL */
/*****************************************/
PROC REPORT NOWD
  DATA = WORK.GRP_SMRY_WEIGHT_DATA;

  COLUMN Weighted_Mean_ETG_Index
      Lower_CnfdncIntrvl_90prcnt
         Upper_CnfdncIntrvl_90prcnt;

  DEFINE Weighted_Mean_ETG_Index    / FORMAT=8.2 CENTER 'Weighted Mean ETG_Index'       ;
  DEFINE Lower_CnfdncIntrvl_90prcnt / FORMAT=8.2 CENTER '90% Lower Confidence_Interval' ;
  DEFINE Upper_CnfdncIntrvl_90prcnt / FORMAT=8.2 CENTER '90% Upper Confidence_Interval' ;
RUN;

/************************************/
/* Create Blank Lines               */
/************************************/
ods text=' ';
ods text=' ';
ods text=' ';

/*****************************************/
/* This prints right beneath PROC REPORT */
/* on the same page due to:              */
/* ODS PDF STARTPAGE = NO  IF ROOM AVLBL */
/*****************************************/
PROC REPORT NOWD
  DATA = WORK.GRP_SMRY_WEIGHT_DATA;

  COLUMN Cost_Compliance;
  DEFINE Cost_Compliance            / CENTER 'Cost Compliance';
RUN;

/************************************/
/* Report Footer                    */
/************************************/
FOOTNOTE
  HEIGHT  = 8pt
  COLOR   = BLUE
  JUSTIFY = LEFT   'Anthem Blue Cross and Blue Shield is the trade name of Anthem Health Plans, Inc. Independent licensee of the '
  JUSTIFY = LEFT   'Blue Cross and Blue Shield Association. ® ANTHEM is a registered trademark of Anthem Insurance Companies, Inc. '
  JUSTIFY = LEFT   'The Blue Cross and Blue Shield names and symbols are registered marks of the Blue Cross and Blue Shield Association.'
  JUSTIFY = LEFT   ' ';

/*********************************************/
/* This Section is used for Closeing ODS     */
/*********************************************/
ODS PDF CLOSE;
ODS PDF(ID=EGPDF) CLOSE;
ODS LISTING CLOSE;

/*********************************************/
/* This Section is only used for Deleting    */
/* the WORK Tables created                   */
/*********************************************/
PROC SQL;
  DROP TABLE WORK.GRP_SMRY_DETAIL_DATA;
  DROP TABLE WORK.GRP_SMRY_WEIGHT_DATA; 
QUIT;

%MEND GRP_SMRY_Rprt;

/************************************/
/* Create Group Summary Temp Table  */
/*   This already exclude Providers */
/*   with No Data                   */
/************************************/
PROC SQL;
CREATE TABLE WORK.GROUP_SUMMARY_TEMP AS (
SELECT DISTINCT
    ST_CD          AS xST_CD,
    ST_CATEGORY_CD AS xST_CAT_CD,
    GRP_ID         AS xGRP_ID,
    SPECIALTY      AS xSPCLTY,
    GRP_NM         AS xGRP_NM
FROM
    xOut.GROUP_SUMMARY
WHERE
    PROV_ID NOT IN ('')

);
QUIT;

PROC SORT
  DATA = WORK.GROUP_SUMMARY_TEMP;
  BY xST_CD
     xST_CAT_CD
     xSPCLTY
     xGRP_ID;
RUN;

DATA _NULL_;
SET  WORK.GROUP_SUMMARY_TEMP;

/******************************************/
/* Call GRP_SMRY_DataSet pass values      */
/******************************************/
    CALL EXECUTE ('%GRP_SMRY_DataSet('!!xST_CD!!',
                                     '!!xST_CAT_CD!!',
                                     '!!xGRP_ID!!',
                                     '!!xSPCLTY!!')
                  ');

/******************************************/
/* Call GRP_SMRY_Rprt PDF Report          */
/******************************************/
    CALL EXECUTE ('%GRP_SMRY_Rprt('!!xST_CD!!',
                                  '!!xST_CAT_CD!!',
                                  '!!xGRP_ID!!',
                                  '!!xGRP_NM!!',
                                  '!!xSPCLTY!!')
                  ');

RUN;

[Cynthia_sas]

Hi:

  I would recommend that you work with Tech Support on this. It looks like you might have too many instances of PDF open based on this part of your code:

ODS PDF CLOSE;

ODS PDF(ID=EGPDF) CLOSE;

ODS LISTING CLOSE;

and I don't understand why you're even closing the LISTING destination, since it's never open in EG unless you open it, so that's a puzzle. But this is the kind of thing where someone needs to try to replicate the problem and to do that, they would need sample data and your code for testing. Since your data is probably confidential, Tech Support are the only ones who can really take a sample dataset, try to replicate your results, using the same version of SAS, EG and operating system and have a private conversation with you about their findings (keeping the data confidential).

  To open a track with Tech Support, send email to support@sas.com, or fill out the form at this link: http://support.sas.com/ctx/supportform/createForm or you can call them at 919-677-8008 to open a track.

cynthia

[Quentin]

Hi,

I would start by taking a look at the values of the variables you are using in the call execute.  Perhaps there is an odd character in one of those (comma, accidental macro trigger, etc.)  If you can use brute force debugging to find out if there is a certain record in work.group_summary_temp, that would be great.

If it's not that, and SAS really seems to hang, then it's tougher to figure out.  One thing to try with CALL EXECUTE is to wrap the macro call in %NRSTR(), so that instead of executing the macro call it just writes the macro invocation. 

As in:

   CALL EXECUTE ('%nrstr(%GRP_SMRY_DataSet('||xST_CD

                                     ||','||xST_CAT_CD

                                     ||','||xGRP_ID

                                     ||','||xSPCLTY

                                     ||'))'

                  );

HTH,

--Q.

[rch1292]

Hi,

I've checked the data and no funny characters are getting in.  I even tried to eliminate unneeded "CLOSE"  Still same results.

I can do this and no issues.  My first thought that it is hanging up because SAS wants to load the PDF OBJECTS in the project after creating them.

FIRSTOBS = 1 OBS = 20

FIRSTOBS = 21 OBS = 20

FIRSTOBS = ... OBS = 20

FIRSTOBS = 1000 OBS = 20

If my process / logic / code is a bit strange, its because I am just starting to learn ODS and did a lot of copy paste.

If there is some coding / process that you can demonstrate using existing dataset (SASHELP) that would be awesome!!!!

My goal is to create a PDF for each instance.

Say 500 colleges students; Each student would get a PDF containing the grades  then wieghted grades compared to other students in the same class.

Student ID     Student Name    Subject          Grade

56456df2       Babe Ruth         Math               98.4

56456df2       Homer Simpson Math               18.4

56456df2       Babe Ruth         Accounting      62

56456df2       Cary Grant        Science           88.8

PDF to Babe Ruth

Student ID     Student Name    Subject          Grade     Weighted Avg

56456df2       Babe Ruth         Math               98.4           58.4

56456df2       Babe Ruth         Accounting      62              62

Student Wieght Average  = 80.2

[ballardw]

Does the program execute if you only use one record from WORK.GROUP_SUMMARY_TEMP? (Data set option OBS=1)

If so it could be that the number of records you are useing is overwhelming the ods system. I would increment the number of records processed to see how much longer adding 5 or 10 records adds to the process.

[Quentin]

Another idea.

Try ruling out that it's a call execute problem (you're asking call execute to generate a lot of code).

Can you replicate the problem with a simple macro that runs:

%do i=1 to 1000;

  %Grp_Smry_DataSet()

  %Grp_SMRY_Rprt()

%end;

So just making the same pdf 1000 times (or 1000 pdfs with the same data)?

Consider an alternate method for generarting the code rather than call execute??

To test your EG output theory, can you turn off automatic display of results and automatic addition of results to the process flow?

[rch1292]

Hi,

It produces all PDFs, but just hangs after last output.

Quentin,  Tried your examples, and made sure the auto results are off...   still hangs

What I am trying to do is

Say I have 1000 students and want to produce a PDF for each of them.

How can I achieve this.

Any sample code would be great....  using SASHELP dataset.....

[Quentin]

When you do the full run and it hangs, how many PDF reports does it produce before hanging?

if you add:

proc printto log="/im/data..../mylog.log" new;

run;

at the top of your code and

proc printto; run;

at the end, do you get a (partial) log file that shows anything interesting?

[Cynthia_sas]

Hi: to get a new file for every region in sashelp.shoes, run this code using NEWFILE= option. (note that I limited the output to 4 regions in my where statement because there is no point in creating 10 files to illustrate NEWFILE=). The first region (Asia) will be in REGION1.PDF, the second region (Canada) will be in REGION2.PDF, etc, etc. Note that some of the PDF files will be just 1 page files (if all the obs for that region fits on 1 page) or could be more than 1 page of output.

Cynthia

options nobyline;

proc sort data=sashelp.shoes out=shoes;
  by region;
  where region in ('Asia', 'Canada', 'Pacific' , 'Eastern Europe');
run;
  
ods pdf file='c:\temp\region1.pdf' newfile=bygroup;

proc print data=shoes;
  title 'For #byval1';
  by region;
  pageby region;
  var region subsidiary product sales inventory returns;
run;

ods pdf close;

[rch1292]

Cynthia,

How can I modify or change so that the name of the PDF is  "Asia"  or "Canada"   byregion.

Thanks

[Cynthia_sas]

Hi:

That is the tradeoff. For the automatic NEWFILE= option, you cannot change the name of the PDF, that is based on what you specify for the FILE= option. So a macro-based approach is the only way to control the name of the PDF file. I do not get into examples of CALL EXECUTE on my forum postings because too many beginner level people could be confused about pursuing the use of CALL EXECUTE when they do not fully understand the technique or implications of that approach. Below is a very simple macro program that will execute the program I posted and create one file (using &OUTNAME) for every time the macro program is invoked. There are methods other than CALL EXECUTE to automate the macro program. If you want to pursue a CALL EXECUTE approach, then I would recommend that you work with Tech Support.

Cynthia

%macro eachreg(region=, outname=);

options nobyline;

** extract the desired region;
proc sort data=sashelp.shoes out=shoes;
  by region;
  where region = "&region";
run;
     
** use the OUTNAME parameter to provide the name of the PDF file;
ods pdf file="c:\temp\&outname..pdf" newfile=bygroup;

proc print data=shoes;
title 'For #byval1';
by region;
pageby region;
var region subsidiary product sales inventory returns;
run;

ods pdf close;
%mend eachreg;

%eachreg(region=Asia, outname=as)
%eachreg(region=Eastern Europe, outname=ee)
%eachreg(region=Canada, outname=ca)
%eachreg(region=Pacific, outname=pa)

[rch1292 · Accepted solution]

Hi Cynthia,

I appologize it's taken me so long to respond back.

I have this issue resolved.  This issue took a bit to figure out as I am still new to ODS.

One of our SAS Consultants was able to direct me to a solution and with other Online Documents, gave me what I was looking for.

Hope this helps others as well.

My Code for the PDF..

/******************************************/
/* MACRO: CREATE DATA FOR REPORT PDF      */
/******************************************/
%MACRO mGroup_Info( xST_CD, xST_CAT_CD, xGRP_ID, xSPCLTY );

/******************************************/
/* Create Cost_Quality Compliance Data    */
/******************************************/
PROC SQL;
CREATE TABLE Work.Cst_Qlty_Cmplnc AS (
SELECT DISTINCT
    . . .
FROM
WHERE
    xGrp.ST_CD          = "&xST_CD."
AND xGrp.ST_CATEGORY_CD = "&xST_CAT_CD."
AND xGrp.GRP_ID         = "&xGRP_ID."
AND xGrp.SPECIALTY      = "&xSPCLTY."
);
QUIT;

DATA Work.Cst_Qlty_Cmplnc;
SET  Work.Cst_Qlty_Cmplnc;
  FORMAT  NCQABTE    $10.;
  FORMAT  RHICmplnc  $10.;
  FORMAT  QltyCmplnc $10.;
  FORMAT  CstCmplnc  $10.;

/* this field is NCQA/BTE External Recognition quality */
  IF EXTERNAL_REC = 'Y' THEN NCQABTE = 'YES';
  ELSE NCQABTE = 'NO';

/* this field is rhi quality */
       IF CI_MET  = 'Y'  THEN RHICmplnc = 'YES';
  ELSE IF CI_MET  = 'N'  THEN RHICmplnc = 'NO';
  ELSE IF CI_MET  = 'DK' THEN RHICmplnc = 'DK';
  ELSE RHICmplnc = 'NO DATA';

/* this field is final quality */
  IF QUALITY = 'Y' THEN QltyCmplnc = 'YES';
  ELSE QltyCmplnc = 'NO';

/* this field is Cost Compliance */
       IF  FLAG30_OE_RATIO = 'DK' THEN CstCmplnc = 'DK';
  ELSE IF  FLAG30_OE_RATIO = 'A'  THEN CstCmplnc = 'YES';
  ELSE IF  FLAG30_OE_RATIO = 'B'  THEN CstCmplnc = 'YES';
  ELSE IF  FLAG30_OE_RATIO = 'C'  THEN CstCmplnc = 'YES';
  ELSE IF  FLAG30_OE_RATIO = 'D'  THEN CstCmplnc = 'NO';
  ELSE IF  FLAG30_OE_RATIO = 'E'  THEN CstCmplnc = 'NO';
  ELSE CstCmplnc = 'NO DATA';

  KEEP
      NCQABTE
      RHICmplnc
      QltyCmplnc
      CstCmplnc ;
RUN;

/******************************************/
/* OTHER SAS Data                         */
/******************************************/

. . . . . . . . . . . .

%MEND mGroup_Info;

/******************************/
/* MACRO: Create Dummy Table  */
/******************************/
%MACRO mGroup_Dummy;

DATA Work.Cost_Summary_Dummy;
  Cost_Summary = 'NO DATA';
RUN;

/******************************************/
/* OTHER SAS Data                         */
/******************************************/

. . . . . . . . . . . .

%MEND mGroup_Dummy;

/************************************/
/* TITLE for New York Report        */
/************************************/
%MACRO TITLE2( xST_CAT_CD );

TITLE2
  JUSTIFY = CENTER '^S={FONT_FACE=Arial FONT_SIZE=12pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=CENTER}New York - ' "&xST_CAT_CD.";

%MEND TITLE2;

/************************************/
/* PDF REPORT OUTPUT                */
/************************************/
%MACRO GRP_SMRY_Rprt( xST_CD, xST_CAT_CD, xGRP_ID, xSPCLTY, xGRP_NM, xLogo, xTITLE );;

/* Modify the PDF page properties */
OPTIONS
   PAPERSIZE=LEGAL
   ORIENTATION=LANDSCAPE
   LEFTMARGIN="0.2cm"
   RIGHTMARGIN="0.2cm"
   TOPMARGIN="0.5cm"
   BOTTOMMARGIN="0.5cm";

/* Close the LISTING destination */
ODS LISTING CLOSE;

/* Reset the graphics options  */
GOPTIONS RESET=ALL NODISPLAY;
GOPTIONS NOBORDER DEVICE=SASPRTC;

/* No Dates or Notes for PDF  */
OPTIONS NONOTES NODATE;

/* No Results returned to SAS */
ODS NORESULTS;

/* PDF output Destination */
ODS PDF FILE = "&xExport_Path./&xPGM_MSRMNT_YEAR_NBR._&xPGM_CD._&xPGM_PRD_CD._&xRPT_LVL_CD._&xRPT_TYPE_CD._&xENCRYPTN_CD._&xRPT_NM_CD._&xRPT_GNRTN_DT._&xST_CD._&xSPCLTY._&xGRP_ID._&xTPAI_ID._&xTPAI_TYPE_CD..pdf"
               STYLE=Journal NOTOC STARTPAGE=NEVER COMPRESS=9;

/************************************/
/* Used for PDF and should be       */
/*    something distinct that would */
/*    not be in existing data.      */
/* A unique character               */
/************************************/
ODS ESCAPECHAR='^';

/************************************/
/* Logo and Title for Report        */
/************************************/
TITLE1
  JUSTIFY = LEFT   '^S={PREIMAGE="&xLogo_Path.&xLogo..jpg"}'
  JUSTIFY = CENTER '^S={FONT_FACE=Arial FONT_SIZE=14pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=CENTER}GROUP REPORT';

/************************************/
/* Check if State is New York and   */
/* add addition to Title.           */
/************************************/
%IF &xST_CD. = 'NY' %THEN %TITLE2( &xST_CAT_CD. );

/************************************/
/* Add addition to Title.           */
/************************************/
TITLE3
  JUSTIFY = CENTER '^S={FONT_FACE=Arial FONT_SIZE=10pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=CENTER}' "&xGRP_ID." ' - ' "&xGRP_NM." ' - ' "&xSPCLTY."
  JUSTIFY = CENTER '^S={FONT_FACE=Arial FONT_SIZE=10pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=CENTER}' "&xPGM_CD." ' Claims Incurred Date: ' "&CLM_PRD_STRT_DT." ' - ' "&CLM_PRD_END_DT.";

/************************************/
/* Add Footnote to PDF.             */
/************************************/
FOOTNOTE
  JUSTIFY = LEFT   '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=LEFT}The Blue Cross and Blue Shield names and symbols are registered marks of the Blue Cross and Blue Shield Association.'
  JUSTIFY = LEFT   '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=LEFT}Blue Cross and Blue Shield Association. ® ANTHEM is a registered trademark of Anthem Insurance Companies, Inc.      '
  JUSTIFY = LEFT   '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=LEFT}Anthem Blue Cross and Blue Shield is the trade name of Anthem Health Plans, Inc. Independent licensee of the        ';

ODS PDF STARTPAGE=NOW;

PROC REPORT
  DATA = Work.Cst_Qlty_Cmplnc CENTER MISSING

  style(report)={FONT_FACE   = "Arial"
                 FONT_SIZE   = 10pt
                 FONT_WEIGHT = LIGHT
                 FONT_STYLE  = ROMAN
                 }
  style(header)={FONT_FACE   = "Arial"
                 FONT_SIZE   = 10pt
                 FONT_WEIGHT = BOLD
                 FONT_STYLE  = ROMAN
                 }
  style(column)={FONT_FACE   = "Arial"
                 FONT_SIZE   = 10pt
                 FONT_WEIGHT = LIGHT
                 FONT_STYLE  = ROMAN
                 };

/*************************************/
/* First Column is the Report Header */
/*************************************/
  COLUMN
   ('^S={FONT_FACE=Arial FONT_SIZE=12pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=CENTER}Group Quality and Efficiency Designation'
         CstCmplnc
         NCQABTE
         RHICmplnc
         QltyCmplnc);

  DEFINE CstCmplnc    /       CENTER 'Cost Recognition';
  DEFINE NCQABTE      /       CENTER 'Quality NCQA-BTE Recognition';
  DEFINE RHICmplnc    /       CENTER 'Quality RHI Recognition';
  DEFINE QltyCmplnc   /       CENTER 'Quality Recognition';
RUN;

ODS TEXT = '^{NEWLINE 2}';

PROC REPORT
  DATA = Work.Cst_Qlty_Wghtd_Lwr_Upr CENTER MISSING

  style(report)={FONT_FACE   = "Arial"
                 FONT_SIZE   = 10pt
                 FONT_WEIGHT = LIGHT
                 FONT_STYLE  = ROMAN
                 }
  style(header)={FONT_FACE   = "Arial"
                 FONT_SIZE   = 10pt
                 FONT_WEIGHT = BOLD
                 FONT_STYLE  = ROMAN
                 }
  style(column)={FONT_FACE   = "Arial"
                 FONT_SIZE   = 10pt
                 FONT_WEIGHT = LIGHT
                 FONT_STYLE  = ROMAN
                 };

/*************************************/
/* First Column is the Report Header */
/*************************************/
  COLUMN
   ('^S={FONT_FACE=Arial FONT_SIZE=12pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=CENTER}Weighted Mean ETG Cost Index & Quality Confidence Intervals'
         Weighted_Mean_ETG_Index
         Cst_Lower_CnfdncIntrvl_90prcnt
         Cst_Upper_CnfdncIntrvl_90prcnt
         GroupScore
         Qlty_Lower_CnfdncIntrvl_90prcnt
         Qlty_Upper_CnfdncIntrvl_90prcnt);

  DEFINE Weighted_Mean_ETG_Index         /       FORMAT=8.2 CENTER 'Weighted Mean ETG Index'            ;
  DEFINE Cst_Lower_CnfdncIntrvl_90prcnt  /       FORMAT=8.2 CENTER 'Cost 90% Lower Confidence Limit'    ;
  DEFINE Cst_Upper_CnfdncIntrvl_90prcnt  /       FORMAT=8.2 CENTER 'Cost 90% Upper Confidence Limit'    ;
  DEFINE GroupScore                      /       FORMAT=8.2 CENTER 'Quality Score'                      ;
  DEFINE Qlty_Lower_CnfdncIntrvl_90prcnt /       FORMAT=8.2 CENTER 'Quality 90% Lower Confidence Limit' ;
  DEFINE Qlty_Upper_CnfdncIntrvl_90prcnt /       FORMAT=8.2 CENTER 'Quality 90% Upper Confidence Limit' ;
RUN;

/*************************************/
/* Add Footnote to Report            */
/*************************************/
ODS TEXT = '^{NEWLINE 1}';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=BOLD  FONT_STYLE=ROMAN} Quality Compliance:';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN} 1) A Group must have at least 10 qualifying events (member-measures) to receive a quality compliance score.';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN}      If the quality 90 percent upper confidence limit is greater than or equal to 0.7, then quality designation = Yes ';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN} 2) Additionally, if the group has received a relevant NCQA or BTE designation, quality designation = Yes';
ODS TEXT = '^{NEWLINE 1}';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=BOLD  FONT_STYLE=ROMAN} Cost Compliance:';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN} 1) If lower confidence limit  <= 0.95 then cost compliance = Yes';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN} 2) If lower confidence limit  >  0.95 then cost compliance = No';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN} 3) If cost ETG index and confidence limits are not calculated due';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN}      to low volume then cost compliance = DK (Dont Know)';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN} 4) The group weighted mean ETG index is calculated in a two-step process:';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN}      First, compute the weighted ETG index for each base ETG = (volume for base ETG x ETG index)';
ODS TEXT = '^S={FONT_FACE=Arial FONT_SIZE=8pt FONT_WEIGHT=LIGHT FONT_STYLE=ROMAN}      Second, sum and average the results = (Sum of all weighted ETG indices / total volume for group)';

ODS PDF STARTPAGE=NOW;

PROC SQL NOPRINT;
SELECT COUNT(*) INTO :Rpt_Cnt
FROM WORK.Cost_Summary;
QUIT;

/*************************************/
/* Check Data Exist for this report  */
/*************************************/
%IF &Rpt_Cnt > 0 %THEN %DO;
PROC REPORT
  DATA = WORK.Cost_Summary MISSING

  style(report)={FONT_FACE   = "Arial"
                 FONT_SIZE   = 10pt
                 FONT_WEIGHT = LIGHT
                 FONT_STYLE  = ROMAN
                 }
  style(header)={FONT_FACE   = "Arial"
                 FONT_SIZE   = 10pt
                 FONT_WEIGHT = BOLD
                 FONT_STYLE  = ROMAN
                 }
  style(column)={FONT_FACE   = "Arial"
                 FONT_SIZE   = 10pt
                 FONT_WEIGHT = LIGHT
                 FONT_STYLE  = ROMAN
                 };

/*************************************/
/* First Column is the Report Header */
/*************************************/
  COLUMN
   ('^S={FONT_FACE=Arial FONT_SIZE=12pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=CENTER}Group Cost Summary'
    Physician_Id
    Physician_Name
    Episode_Vol
    SRT_CST
    Ttl_Dlrs_Allwd
    Expctd_Dlrs_Allwd
    Allwd_Dlrs_per_Epsd
    Expctd_Alwd_Dlrs_per_Epsd
    Prfrmnc_Ratio_Cmptd);

/***************************************/
/* Order by is seperated and not shown */
/***************************************/
  DEFINE Physician_Id              /       FORMAT=$100.                    'Physician ID'           ;
  DEFINE Physician_Name            /       FORMAT=$100.                    'Physician Name'         ;
  DEFINE Episode_Vol               /       FORMAT=COMMA10.   ANALYSIS  SUM 'Episode Vol'            ;
  DEFINE SRT_CST                   / ORDER ORDER=INTERNAL DESCENDING NOPRINT;
  DEFINE Ttl_Dlrs_Allwd            /       FORMAT=COMMA10.   ANALYSIS  SUM 'Total $ Allowed'        ;
  DEFINE Expctd_Dlrs_Allwd         /       FORMAT=COMMA10.   ANALYSIS  SUM 'Expected $ Allowed'     ;
  DEFINE Allwd_Dlrs_per_Epsd       /       FORMAT=COMMA10.   COMPUTED      'Allowed $ per Episode'  ;
  DEFINE Expctd_Alwd_Dlrs_per_Epsd /       FORMAT=COMMA10.   COMPUTED      'Expected $ per Episode' ;
  DEFINE Prfrmnc_Ratio_Cmptd       /       FORMAT=8.2        COMPUTED      'ETG Index'              ;

  COMPUTE Allwd_Dlrs_per_Epsd;
      Allwd_Dlrs_per_Epsd = Ttl_Dlrs_Allwd.SUM / Episode_Vol.SUM ;
  ENDCOMP;

  COMPUTE Expctd_Alwd_Dlrs_per_Epsd;
      Expctd_Alwd_Dlrs_per_Epsd = Expctd_Dlrs_Allwd.SUM / Episode_Vol.SUM;
  ENDCOMP;

  COMPUTE Prfrmnc_Ratio_Cmptd;
      Prfrmnc_Ratio_Cmptd = Ttl_Dlrs_Allwd.SUM / Expctd_Dlrs_Allwd.SUM;
  ENDCOMP;

  RBREAK AFTER /SUMMARIZE;
RUN;
%END;
%ELSE %DO;
/***********************************************/
/*      PRINT   NO_DATA_FOUND   ON REPORT      */
/***********************************************/
PROC REPORT
  DATA = WORK.Cost_Summary;

/*************************************/
/* First Column is the Report Header */
/*************************************/
  COLUMN
   ('^S={FONT_FACE=Arial FONT_SIZE=12pt FONT_WEIGHT=BOLD FONT_STYLE=ROMAN JUST=CENTER}Group Cost Summary'
    Cost_Summary);
  DEFINE Cost_Summary / CENTER 'Cost Summary' STYLE(COLUMN)=[CELLWIDTH=100 JUST=CENTER];
RUN;
%END;

/*****************************/
/* Close the PDF destination */
/*****************************/
ODS PDF CLOSE;

/*****************************/
/* Open the LISTING          */
/*****************************/
ODS LISTING;

/******************************/
/* Reset the graphics options */
/******************************/
GOPTIONS RESET=ALL;

/******************************/
/* Reset the RESULTS; NOTES;  */
/******************************/
ODS RESULTS;
OPTIONS NOTES;

%MEND GRP_SMRY_Rprt;

/******************************/
/* Set OBS for Sample Run     */
/******************************/
option obs=15;

/************************************/
/* Create Group Summary Temp Table  */
/*   This already exclude Providers */
/*   with No Data                   */
/************************************/
PROC SQL;
CREATE TABLE WORK.Group_Temp AS (
SELECT DISTINCT
    TRIM(ST_CD)          AS ST_CD,
    TRIM(ST_CATEGORY_CD) AS ST_CATEGORY_CD,
    TRIM(GRP_ID)         AS GRP_ID,
    TRIM(SPECIALTY)      AS SPECIALTY,
    TRIM(GRP_NM)         AS GRP_NM,
    TRIM(
    CASE
        WHEN TRIM(ST_CD) = 'CA' THEN 'Clfrna_Logo'
        WHEN TRIM(ST_CD) = 'GA' THEN 'Gorgia_Logo'
        WHEN TRIM(ST_CD) = 'NY' THEN 'Empire_Logo'
        ELSE                         'Anthem_Logo'
    END) AS xLogo
FROM
    xOut.GROUP_ID
WHERE
    ST_CD          = 'CT'
);
QUIT;

/******************************/
/* ReSet OBS for MAX ROWS     */
/******************************/
option obs=max;

/***********************/
/* Create Dummy Data   */
/***********************/
%mGroup_Dummy;

DATA _NULL_;
SET  WORK.Group_Temp ;
  BY ST_CD ST_CATEGORY_CD GRP_ID SPECIALTY;

/************************/
/* Create file to store */
/* Macro and Values     */
/************************/
  FILE "&xExport_Path.mGroup_Info_Mcro.sas";

  /* For Data */
  stmt = CATS('%mGroup_Info(xST_CD=',ST_CD,
                         ', xST_CAT_CD=',ST_CATEGORY_CD,
                         ', xGRP_ID=',GRP_ID,
                         ', xSPCLTY=',SPECIALTY,");");

/**************************/
/* store Macro and Values */
/**************************/
  PUT stmt;

  /* For PDF */
  stmt = CATS('%GRP_SMRY_Rprt(xST_CD=',ST_CD,
                           ', xST_CAT_CD=',ST_CATEGORY_CD,
                           ', xGRP_ID=',GRP_ID,
                           ', xSPCLTY=',SPECIALTY,
                           ', xGRP_NM=',GRP_NM,
                           ', xLogo=',xLogo,");");

/**************************/
/* store Macro and Values */
/**************************/
  PUT stmt;

RUN;

/******************************************/
/* Open and Execute File Macro and Values */
/******************************************/
%INCLUDE "&xExport_Path.mGroup_Info_Mcro.sas";

/***************************/
/* Drop all Tempory Tables */
/***************************/
PROC SQL;
  DROP TABLE Work.Group_Temp;

  DROP TABLE Work.Cst_Qlty_Cmplnc;
  . . .
  . . .
QUIT;