/**********************************************************************\

PROGRAM:        C:\MEPS\PROG\EXAMPLE08.SAS

DESCRIPTION:  	THIS EXAMPLE SHOWS HOW TO COMPUTE PRESCRIBED
		         MEDICINE EXPENDITURES ASSOCIATED WITH CANCER 
		         CONDITIONS.
		
		         IN ADDITION TO THE MEDICAL CONDITIONS AND FULL-
		         YEAR FILES, THE PRESCRIBED MEDICINES EVENT FILE AND 
		         THE CLNK FILE ARE USED.
		         THE PMED EXPENDITURES ARE SUMMED TO THE EVENT 
		         LEVEL.
		         MODIFIED SOURCE OF PAYMENT (SOP) CATEGORIES
		         ARE CREATED AS COLUMN CATEGORIES.
		         ROW CATEGORIES ARE AGE AND RACE/ETHNICITY (FROM
		         THE FULL-YEAR FILE).
		         USE PROC TABULATE TO COMPUTE AND DISPLAY OUTPUT.

INPUT FILES:  	C:\MEPS\DATA\H79.SAS7BDAT (2003 FULL-YEAR DATA FILE)
		         C:\MEPS\DATA\H78.SAS7BDAT (2003 MEDICAL CONDITIONS FILE)
		         C:\MEPS\DATA\H77I1.SAS7BDAT (2003 CLNK FILE)
			         THIS FILE LINKS CONDITIONS TO EVENTS
		         C:\MEPS\DATA\H77A.SAS7BDAT (2003 PRESCRIBED MED. FILE)

\**********************************************************************/

FOOTNOTE 'PROGRAM: C:\MEPS\PROG\PMED\EXAMPLE08.SAS';

LIBNAME CDATA V8 'C:\MEPS\DATA';

TITLE1 'AHRQ MEPS DATA USERS WORKSHOP -- JULY 2006';
TITLE2 'PRESCRIBED MEDICINES EXPENDITURES FOR CANCER CONDITIONS';

PROC FORMAT;
	VALUE AGECAT 
	.='TOTAL'
	1=' < 18'
	2='18-64'
	3='65+'
	;
	VALUE RACETHNB
	1 = 'HISPANIC'
	2 = 'BLACK'
	3 = 'ASIAN'
	4 = 'OTHER';
RUN;
 
/***** USE CCCODEX VALUES TO SELECT PEOPLE WITH CANCER FROM 2003 CONDITION FILE. *****/

DATA COND03 (KEEP=DUPERSID CONDIDX);
	SET CDATA.H78 (KEEP=DUPERSID CCCODEX CONDIDX
	           WHERE =('011' <= CCCODEX <= '045' ) );
RUN;

/***** GET PRESCRIBED MEDICINES EXPENSES FROM PMED FILE. *****/


PROC SORT DATA= CDATA.H77A (KEEP= DUPERSID RXRECIDX LINKIDX RXSF03X RXMD03X RXPV03X
				RXMR03X RXXP03X RXNDC)
		OUT= PMEDACQ;
	BY LINKIDX RXRECIDX;
RUN;

TITLE3 'ILLUSTRATION OF PMED RECORDS AT THE ACQUISITION LEVEL';
TITLE4 'PRE-SELECTED PERSON';

PROC PRINT DATA= PMEDACQ LABEL ;
	VAR DUPERSID LINKIDX RXRECIDX RXXP03X RXSF03X RXPV03X RXMD03X RXMR03X ;
	LABEL 	RXXP03X = 'TOTAL EXP'
		      RXSF03X = 'OOP'
		      RXMD03X = 'MEDICAID'
		      RXMR03X = 'MEDICARE'
		      RXPV03X = 'PRIVATE' ;
   WHERE DUPERSID = '20953015' ;
RUN;

/***** SUM PMED EXPENSES TO EVENT LEVEL *****/

DATA PMEDEVNT (KEEP= DUPERSID EVNTIDX OOP MEDICARE MEDICAID PRIVATE OTHER TOTRX03 RXNDC);
	SET PMEDACQ;
	BY LINKIDX RXRECIDX;
	ARRAY EXPVARS{5} OOP MEDICARE MEDICAID PRIVATE TOTRX03  ;
	IF FIRST.LINKIDX
		THEN DO XX = 1 TO 5;
			EXPVARS{XX} = 0;
		     END;
	OOP+RXSF03X;
	MEDICARE+RXMR03X;
	MEDICAID+RXMD03X;
	PRIVATE+RXPV03X;
	TOTRX03+RXXP03X;
	EVNTIDX=LINKIDX;
	IF LAST.LINKIDX
		THEN
		DO;
			OTHER= ROUND(TOTRX03-(MEDICARE+MEDICAID+PRIVATE+OOP));
			OUTPUT;
		END;
	LABEL 	OOP = 'OUT-OF-POCKET'
		      MEDICARE = 'MEDICARE'
		      MEDICAID = 'MEDICAID'
		      PRIVATE = 'PRIVATE'
		      OTHER = 'OTHER'
		      TOTRX03 = 'TOTAL PMED EXPENSES';
RUN;

TITLE3 'ILLUSTRATION OF PMED RECORDS AT THE EVENT LEVEL';
TITLE4 'PRE-SELECTED PERSON';

PROC PRINT DATA= PMEDEVNT ;
	VAR DUPERSID EVNTIDX RXNDC TOTRX03 OOP PRIVATE MEDICARE MEDICAID OTHER;
   WHERE DUPERSID = '20953015' ;
RUN;
	
/***** MERGE CONDITIONS FILE TO PRESCRIBED MEDICINE EVENTS THROUGH CLNK FILE *****/

DATA CLNK;
	SET CDATA.H77I1 (KEEP=CONDIDX EVNTIDX);
RUN;

PROC SORT DATA=CLNK;
	BY CONDIDX;
RUN;

PROC SORT DATA=COND03;
	BY CONDIDX;
RUN;

/***** MERGE BY CONDIDX TO GET EVNTIDS ASSOCIATED WITH CANCER EVENTS *****/

DATA CONDCLNK;
	MERGE COND03 (IN=A) CLNK (IN=B) ;
	BY CONDIDX;
	IF A;
RUN;

PROC SORT DATA=PMEDEVNT;
	BY  EVNTIDX;
RUN;

PROC SORT DATA=CONDCLNK;
	BY EVNTIDX;
RUN;

/***** NOW MERGE CONDITIONS FILE WITH EVENTIDS TO PMED EVENT FILE. *****/
/***** ONLY KEEP RECORDS THAT MATCH.                               *****/

DATA CONDPMED;
	MERGE CONDCLNK (IN=A) PMEDEVNT(IN=B) ;
	BY EVNTIDX;
	IF A AND B; 
	
RUN;

PROC SORT DATA= CONDPMED;
	BY EVNTIDX CONDIDX;
RUN;

TITLE3 'ILLUSTRATION OF DUPLICATE EVENTS/EXPENDITURES';
TITLE4 'PRE-SELECTED PERSON';

PROC PRINT DATA= CONDPMED ;
	VAR DUPERSID CONDIDX EVNTIDX TOTRX03 OOP 
	    PRIVATE MEDICARE MEDICAID OTHER;
   WHERE DUPERSID = '20953015' ;
RUN;

/***** DE-DUPLICATE EXPENDITURES *****/

PROC SORT DATA=CONDPMED NODUPKEY;
	BY EVNTIDX;
RUN;

TITLE3 'ILLUSTRATION OF DE-DUPLICATED EVENTS/EXPENDITURES';
TITLE4 'PRE-SELECTED PERSON';

PROC PRINT DATA=CONDPMED ;
	VAR DUPERSID CONDIDX EVNTIDX TOTRX03 OOP 
	    PRIVATE MEDICARE MEDICAID OTHER;
   WHERE DUPERSID = '20953015' ;
RUN;

/***** USE FULL-YEAR FILE TO GET AGE AND RACE/ETHNICITY VARIABLES.   *****/
/***** SET AGE AS LATEST VALID AGE AND RE-CATEGORIZE RACE/ETHNICITY. *****/

DATA PUF79 (KEEP= DUPERSID AGECAT  RACETHNX PERWT03F); 
	SET CDATA.H79 (KEEP= DUPERSID AGE: RACETHNX RACEX 
	                PERWT03F);
	IF AGE03X>=0 THEN AGE=AGE03X;
	ELSE IF AGE53X>=0 THEN AGE=AGE53X;
	ELSE IF AGE42X>=0 THEN AGE=AGE42X;
	ELSE IF AGE31X>=0 THEN AGE=AGE31X;
	ELSE AGE=AGE03X;
	IF 0<= AGE <18 THEN AGECAT=1;
	ELSE IF 18 <= AGE <65 THEN AGECAT=2;
	ELSE IF AGE>=65 THEN AGECAT=3;
	LABEL 	AGECAT='AGE IN YEARS'
		RACETHNX='RACE/ENTHNICITY';
RUN;

/***** MERGE RACE/ETHNICITY VARIABLES ONTO COND/PMED FILE *****/

DATA TEMP;
	MERGE CONDPMED(IN=A) PUF79 (IN=B);
	BY DUPERSID;
	IF A;
	OOP=OOP/1000;
	PRIVATE=PRIVATE/1000;
	MEDICARE=MEDICARE/1000;
	MEDICAID=MEDICAID/1000;
	OTHER=OTHER/1000;
	TOTRX03=TOTRX03/1000;
RUN;

TITLE3 'DOLLARS IN THOUSANDS';

PROC TABULATE DATA=TEMP MISSING FORMAT=COMMA10.0 ;
	CLASS AGECAT  RACETHNX;
	VAR   TOTRX03 OOP PRIVATE MEDICARE MEDICAID OTHER;
	FORMAT AGECAT AGECAT.   RACETHNX RACETHNB.;  
	TABLE  ALL='TOTAL' AGECAT RACETHNX , 
		TOTRX03*SUM OOP*SUM PRIVATE*SUM MEDICARE*SUM
		MEDICAID*SUM OTHER*SUM
		/RTS=20 ; 
	WEIGHT PERWT03F;
RUN;