/***************************************************************************/
  /* PROGRAM NAME:  CEX INTEGRATED SURVEYS SAMPLE PROGRAM (SAS)              */
  /* FUNCTION: CREATE A WEIGHTED CALENDAR YEAR AVERAGE EXPENDITURE           */
  /*           FOR A SPECIFIED GROUP OF UCC's                                */
  /*                                                                         */
  /*                                                                         */
  /***************************************************************************/

  /***************************************************************************/
  /* STEP1: SET PARAMETERS                                                   */
  /***************************************************************************/

  /*ENTER DATA YEAR*/
    %LET YEAR = 2018;
  /* ENTER SURVEY SOURCE FOR WHICH YOU WISH TO ESTIMATE FROM (INTERVIEW, DIARY, INTEGRATED)*/
	%LET ESTIMATE = INTEGRATED;
  /* ENTER THE UCCs YOU WISH TO ESTIMATE FOR*/
	%LET UCC = ('470111'); 
  /* ENTER A NAME FOR THE ESTIMATE BEING CREATED*/
	%LET AGG = GASOLINE;
 
	%LET YR1 = %SUBSTR(&YEAR, 3, 2);
	%LET YR2 = %SUBSTR(%EVAL(&YEAR + 1), 3, 2);
  /*ENTER LOCATION OF THE UNZIPPED MICRODATA FILE*/
    %LET DRIVE = C:\2018_CEX;

	LIBNAME I&YR1 "&DRIVE\INTRVW&YR1";
	LIBNAME D&YR1 "&DRIVE\DIARY&YR1";


  /***************************************************************************/
  /* STEP2: READ IN ALL NEEDED DATA                                          */
  /* ----------------------------------------------------------------------- */
  /* 1 READ IN THE INTERVIEW AND DIARY FMLY FILES & CREATE MO_SCOPE VARIABLE */
  /* 2 READ IN THE INTERVIEW MTAB/ITAB AND DIARY EXPN/DTAB FILES             */
  /* 3 MERGE FMLY AND EXPENDITURE FILES TO DERIVE WEIGHTED EXPENDITURES      */
  /***************************************************************************/


DATA FMLY (KEEP= NEWID QINTRVYR QINTRVMO WTREP01-WTREP44 FINLWT21 REPWT1-REPWT45 MO_SCOPE SOURCE);

SET D&YR1..FMLD&YR1.1 (IN = DIARY)
    D&YR1..FMLD&YR1.2 (IN = DIARY)
    D&YR1..FMLD&YR1.3 (IN = DIARY)
    D&YR1..FMLD&YR1.4 (IN = DIARY)

    I&YR1..FMLI&YR1.1x (IN = INTERVIEW)
    I&YR1..FMLI&YR1.2  (IN = INTERVIEW)
    I&YR1..FMLI&YR1.3  (IN = INTERVIEW)
    I&YR1..FMLI&YR1.4  (IN = INTERVIEW)
    I&YR1..FMLI&YR2.1  (IN = INTERVIEW);

	BY NEWID;

	ESTIMATE = "&ESTIMATE.";

	ARRAY REPS_A(45) WTREP01-WTREP44 FINLWT21;
    ARRAY REPS_B(45) REPWT1-REPWT45;

	IF INTERVIEW AND ESTIMATE IN ("INTERVIEW","INTEGRATED") THEN DO;
		SOURCE = 'I';
		IF QINTRVMO IN ('01','02','03') AND QINTRVYR = &YEAR. THEN MO_SCOPE = (QINTRVMO - 1);
    		ELSE IF QINTRVMO IN ('01','02','03') AND QINTRVYR = &YEAR. + 1 THEN MO_SCOPE = (4 - QINTRVMO);
    			ELSE MO_SCOPE = 3;

 		DO i = 1 TO 45;
	  	IF REPS_A(i) > 0 THEN REPS_B(i) = (REPS_A(i) * MO_SCOPE / 12);
		END;	
				OUTPUT;
				END;

	IF DIARY AND ESTIMATE IN ("DIARY","INTEGRATED") THEN DO;
		SOURCE = "D";
		MO_SCOPE = 3;

	DO i = 1 TO 45;
	  	IF REPS_A(i) > 0 THEN REPS_B(i) = (REPS_A(i) * MO_SCOPE / 12);
		END;	

				OUTPUT;
				END;

	
	  IF QINTRVYR  THEN 
        SOURCE = 'I';
	  IF WEEKI THEN 
        SOURCE = 'D';

RUN;

DATA EXPEND (KEEP= NEWID SOURCE UCC COST REF_YR);

  SET D&YR1..EXPD&YR1.1 (IN = DIARY)
      D&YR1..EXPD&YR1.2 (IN = DIARY)
      D&YR1..EXPD&YR1.3 (IN = DIARY)
      D&YR1..EXPD&YR1.4 (IN = DIARY)

      I&YR1..MTBI&YR1.1X (IN = INTERVIEW)
      I&YR1..MTBI&YR1.2  (IN = INTERVIEW)
      I&YR1..MTBI&YR1.3  (IN = INTERVIEW)
      I&YR1..MTBI&YR1.4  (IN = INTERVIEW)
      I&YR1..MTBI&YR2.1  (IN = INTERVIEW);
	
  ESTIMATE = "&ESTIMATE.";
  IF UCC IN &UCC.;       

	IF INTERVIEW AND ESTIMATE IN ("INTERVIEW") THEN DO;
		SOURCE = 'I';
		IF (REF_YR = "&YEAR") THEN
			OUTPUT;
			END;
	ELSE IF INTERVIEW AND ESTIMATE = "INTEGRATED" THEN DO; 
		SOURCE = "I"; 
		IF (REF_YR = "&YEAR");
		IF PUBFLAG = "2" THEN
 			OUTPUT;
			END;

	IF DIARY AND ESTIMATE IN ("DIARY") THEN DO;
		SOURCE = 'D';
		COST = COST * 13;
			OUTPUT;
			END;

	ELSE IF DIARY AND ESTIMATE IN ("INTEGRATED") THEN DO; 
		SOURCE = "D"; 
		COST = COST * 13;
		IF PUB_FLAG = "2" THEN
 			OUTPUT;
			END;
	
RUN;	

PROC SORT DATA=EXPEND;
	BY NEWID;
RUN;

DATA PUBFILE (KEEP= NEWID SOURCE UCC WTREP01-WTREP44 FINLWT21 RCOST1-RCOST45 REPWT1-REPWT45 MO_SCOPE COST);
  MERGE FMLY   (IN= INFAM)
        EXPEND (IN= INEXP);
  BY NEWID;
  IF (INEXP AND INFAM);

  IF (COST = .)  THEN 
     COST = 0;
     ARRAY REPS_A(45) WTREP01-WTREP44 FINLWT21;
     ARRAY REPS_B(45) RCOST1-RCOST45;

     DO i = 1 TO 45;
	   IF REPS_A(i) > 0
         THEN REPS_B(i) = (REPS_A(i) * COST);
	     ELSE REPS_B(i) = 0; 	
	 END; 

RUN;

  /***************************************************************************/
  /* STEP3: CALCULATE POPULATIONS                                            */
  /* ----------------------------------------------------------------------- */
  /*  SUM ALL 45 WEIGHT VARIABLES TO DERIVE REPLICATE POPULATIONS            */
  /*  FORMATS FOR CORRECT COLUMN CLASSIFICATIONS                             */
  /***************************************************************************/

PROC SUMMARY NWAY DATA=FMLY SUMSIZE=MAX;
  CLASS SOURCE / MLF;
  VAR REPWT1-REPWT45;
  OUTPUT OUT = POP (DROP = _TYPE_ _FREQ_) SUM = RPOP1-RPOP45;
RUN;

 

  /***************************************************************************/
  /* STEP4: CALCULATE WEIGHTED AGGREGATE EXPENDITURES                        */
  /* ----------------------------------------------------------------------- */
  /*  SUM THE 45 REPLICATE WEIGHTED EXPENDITURES TO DERIVE AGGREGATES/UCC    */
  /*  FORMATS FOR CORRECT COLUMN CLASSIFICATIONS                             */
  /***************************************************************************/


PROC SUMMARY NWAY DATA=PUBFILE SUMSIZE=MAX COMPLETETYPES;
  CLASS SOURCE UCC / MLF;
  VAR RCOST1-RCOST45;
  OUTPUT OUT= AGG (DROP= _TYPE_ _FREQ_) 
  SUM= RCOST1-RCOST45;
RUN;



  /***************************************************************************/
  /* STEP5: CALCULTATE MEAN EXPENDITURES                                     */
  /* ----------------------------------------------------------------------- */
  /* 1 READ IN POPULATIONS AND LOAD INTO MEMORY USING A 3 DIMENSIONAL ARRAY  */
  /*   POPULATIONS ARE ASSOCIATED BY INCLASS, SOURCE(t), AND REPLICATE(j)    */
  /* 2 READ IN AGGREGATE EXPENDITURES FROM AGG DATASET                       */
  /* 3 CALCULATE MEANS BY DIVIDING AGGREGATES BY CORRECT SOURCE POPULATIONS  */
  /*   EXPENDITURES SOURCED FROM DIARY ARE CALULATED USING DIARY POPULATIONS */
  /*   WHILE INTRVIEW EXPENDITURES USE INTERVIEW POPULATIONS                 */
  /* 4 SUM EXPENDITURE MEANS PER UCC INTO CORRECT LINE ITEM AGGREGATIONS     */
  /***************************************************************************/


DATA AVGS1 ;
	MERGE 	AGG
			POP; 
		BY SOURCE; 
		
	   		ARRAY REPS_A(45) RCOST1-RCOST45;
			ARRAY REPS_B(45) RPOP1-RPOP45;
			ARRAY REPS_C(45) MEAN1-MEAN45;
     		DO i = 1 TO 45;
	   	IF REPS_A(i) > 0
         THEN REPS_C(i) = (REPS_A(i) / REPS_B(i));
	     ELSE REPS_C(i) = .; 
		END;	
		RUN;


PROC SUMMARY DATA=AVGS1 NWAY COMPLETETYPES;
  CLASS UCC / MLF;
  VAR MEAN1-MEAN45;
  OUTPUT OUT=AVGS2 (DROP= _TYPE_ _FREQ_) SUM= ;
  /* SUM UCC MEANS TO CREATE AGGREGATION SCHEME */
RUN;

  /***************************************************************************/
  /* STEP6: CALCULTATE STANDARD ERRORS                                       */
  /* ----------------------------------------------------------------------- */
  /*  CALCULATE STANDARD ERRORS USING REPLICATE FORMULA                      */
  /***************************************************************************/

PROC SUMMARY DATA=AVGS1 NWAY COMPLETETYPES;
  VAR MEAN1-MEAN45;
  OUTPUT OUT=AVGS_SUM (DROP= _TYPE_ _FREQ_) SUM= ;
  /* SUM UCC MEANS TO CREATE AGGREGATION SCHEME */
RUN;

DATA &ESTIMATE. (KEEP = UCC &ESTIMATE._MEAN&YR1. &ESTIMATE._SE&YR1. &ESTIMATE._CV&YR1.);
RETAIN UCC &ESTIMATE._MEAN&YR1. &ESTIMATE._SE&YR1. &ESTIMATE._CV&YR1.;
FORMAT 	&ESTIMATE._MEAN&YR1. DOLLAR12.2 
		&ESTIMATE._SE&YR1.   8.2
		&ESTIMATE._CV&YR1.   8.2;
LENGTH	UCC $99.;
SET AVGS2 AVGS_SUM;
  ARRAY RMNS(44) MEAN1-MEAN44;
  ARRAY DIFF(44) DIFF1-DIFF44;
    DO i = 1 TO 44;
      DIFF(i) = (RMNS(i) - MEAN45)**2;
    END;
  IF UCC = " " THEN UCC = "&AGG.";
  &ESTIMATE._MEAN&YR1. = MEAN45;
  &ESTIMATE._SE&YR1. = SQRT((1/44)*SUM(OF DIFF(*)));
  &ESTIMATE._CV&YR1. = (&ESTIMATE._SE&YR1. / &ESTIMATE._MEAN&YR1.) * 100; 
  RUN;