* USER     = your-userId
* PASSWORD = your-password
* PACKAGE  = SAS
* PROJECT  = LIS

OPTION source = no;

/**
   Program created by: Thierry Kruten
   Updated on        : 04 JUN 2007
   Font used to get a nice layout : Courier New (SAS default font)
**/

/*-----------------------*/
/*  SAS ENVIRONMENT DEF  */
/*-----------------------*/
OPTIONS nonotes nodate nofmterr nocenter label nonumber LS=200 PS=30;
TITLE " " ;


/*************************************************************************/
/**                 PART I: MACRO DECLARATIONS                          **/
/*************************************************************************/

/*----------------------------------*/
/*      MACRO PREPARE DATASET       */
/*----------------------------------*/
%MACRO prep ;


** Clean the dataset and create needed variables ;

  DATA prep        (DROP=d5 hweight);
   SET &&&cc.&yy.h (KEEP=country hweight d4 d5 dpi d27 num6574 numge75 married d3);
   * select only records if DPI filled;
     IF dpi  in (. 0)  THEN DELETE;
     * avoid double counting of household records ;
     IF d5   =     3   THEN DELETE;
     * set equivalence scale as square root of number of persons;
     ey      =   dpi   / SQRT(d4);
     * create person weight as hweight times number of persons;
     wt      = hweight * d4;
      * create children weight as hweight times number of persons;
     ct      = hweight * d27;
     * create elderly weight as hweight times number of persons;
     et      = hweight * SUM(num6574,numge75);
     * create dummy variable "elderly" as existence of at least one elderly person within the household ;
     elderly = 0 ;
     IF (SUM(num6574,numge75)>0) THEN elderly = 1 ;
  RUN;

** Top-bottom Coding ;

  * Get the median disposible income (inc) and the mean equivalised income ;
  PROC UNIVARIATE DATA=prep NOPRINT;
    VAR    ey dpi ;
    WEIGHT wt ;
    OUTPUT OUT=temp MEAN=aveey MEDIAN=medey medpi ;
  RUN ;
  DATA _NULL_;
   SET temp;
     CALL SYMPUT("a",aveey);
     CALL SYMPUT("m",medpi);
  RUN;
  * Top-bottom coding ;
  DATA start (DROP= botlin toplin);
   SET prep  ;
     * bottomcoding at one percent equivalized mean;
     botlin = 0.01 * &a;
     IF ey  < botlin THEN ey = botlin ;
     * topcoding at ten times unequivalized median ;
     toplin = 10 * &m;
     IF dpi > toplin THEN ey = (toplin/(SQRT(d4))) ;
  RUN ;
  PROC SORT DATA=start;
    BY country ey ;
  RUN ;

** Define different status of households to calcute several poverty rates ;

  * Calculation of median income (after top-bottm coding) ;
  PROC UNIVARIATE DATA=start NOPRINT;
   VAR    ey;
   WEIGHT wt ;
   OUTPUT OUT=temp MEDIAN=medey ;
  RUN ;
  DATA _NULL_;
   SET temp;
     CALL SYMPUT("mtopBot",medey);
  RUN;

  DATA prepkf (KEEP=country ey eymed wt ct et povlin4 povlin5 povlin6 povlin7 povlin15 poor4
                    poor5 poor6 poor57 poor715 poor15 kidsm d3 d4 d27 married twoPar elderly
               ) ;
   SET  start ;
   * Initialisation of the status of the households ;
     poor4   = 0 ;
     poor5   = 0 ;
     poor6   = 0 ;
     poor57  = 0 ;
     poor715 = 0 ;
     poor15  = 0 ;
     poortp  = 0 ;
     poorsm  = 0 ;
     kidsm   = 0 ;
     twoPar  = 0 ;
	 * Define different threshold to calculate poverty lines ;
     eymed    = &mtopBot       ;
     povlin4  = &mtopBot * 0.4 ;
     povlin5  = &mtopBot * 0.5 ;
     povlin6  = &mtopBot * 0.6 ;
     povlin7  = &mtopBot * 0.75;
     povlin15 = &mtopBot * 1.5 ;

   * Affect the status to the households according to ;

     * 1. level of equivalised income ;
     IF ey < povlin4 THEN poor4    = 1  ;
     IF ey < povlin5 THEN poor5    = 1  ;
     IF ey < povlin6 THEN poor6    = 1  ;

     * 2. Group of income to define children distribution ;
     IF povlin5 < ey <= povlin7  THEN poor57  = 1 ;
     IF povlin7 < ey <= povlin15 THEN poor715 = 1 ;
     IF ey gt povlin15           THEN poor15  = 1 ;

     * 3. Single mother household ;
     IF ( (d27>0) AND ((d3=2) AND (married=0))) THEN kidsm = 1 ;

	   * 4. Two-Parents household ;
	   IF ((married > 0) AND ((d4-d27) > 1)) THEN twoPar = 1 ;

RUN ;

  * Needed for gini and quantiles  ;
  PROC SORT DATA=prepkf ;
    BY country ey ;
  RUN ;

%MEND Prep ;

/*-------------------------------------*/
/*  MACRO: CALCULATE GINI COEFFICIENT  */
/*-------------------------------------*/
%MACRO gini ;

DATA &cc.&yy._1 (KEEP=country gini) ;
 IF _N_ = 1 THEN
   DO  UNTIL (last) ;
     SET prepkf END=last;
        swt   +  wt     ;
        swtey + (wt*ey) ;
   END ;

 SET prepkf END=eof;
  BY country ;

    IF _N_ = 1 THEN
      DO ;
        prewt = 0 ;
        preey = 0 ;
           up = 0 ;
          sum = 0 ;
      END ;

    FORMAT gini 6.3 ;

    cwt    + wt                  ;
    cwtey  + (ey*wt)             ;
    pcwt   = cwt   / swt   * 100 ;
    pcwtey = cwtey / swtey * 100 ;
    up     = (pcwt-prewt) * (pcwtey+preey) ;
    sum    + up                            ;

    prewt = pcwt       ;
    preey = pcwtey     ;
    RETAIN prewt preey ;

    IF eof THEN
      DO ;
         gini = 1 - (sum / 10000) ;
         OUTPUT                   ;
      END ;
RUN;

*----------------   METHOD 2   ----------------- ;
/*
 SET start END=eof;
  BY country ;

     RETAIN swt swtey swt2ey swteycw;
     * Initialise temp variables;
     IF _N_ = 1 THEN
       DO ;
          swt     = 0;
          swtey   = 0; 
          swt2ey  = 0;
          swteycw = 0;
       END ;
	 * Calculation;
     swt     = swt     +  wt        ;
     swtey   = swtey   + (wt*ey)    ;
     swt2ey  = swt2ey  + (wt*wt*ey) ;
     swteycw = swteycw + (swt*wt*ey);
  * Output Gini at the last observation;        
     IF eof THEN
       DO ;
         gini = 100*((2*swteycw-swt2ey)/(swt * swtey)-1);
         OUTPUT                   ;
       END ;
*/

%MEND gini;

/*-----------------------------------*/
/* MACRO: CALCULATE ATKINSON INDEXES */
/*-----------------------------------*/

%MACRO atkin ;

DATA tempo ;
  IF _N_ = 1 THEN
     DO  UNTIL (last) ;
      SET prepkf END=last;
         swt   + wt      ;
         swtey + (wt*ey) ;
    END ;

  SET prepkf ;
   BY country ;
      mwtey = swtey / swt ;
      yy    = (ey/mwtey) ;
RUN ;

DATA &cc.&yy._2 (KEEP=country atk5 atk1);
 SET tempo END=eof;
  BY country ;

  FORMAT atk5 atk1 6.3 ;

  ratio1    = log(yy)           ;
  cwtratio1 + (ratio1*(wt/swt)) ;

  ratio5    = yy**(1-0.5)       ;
  cwtratio5 + (ratio5*(wt/swt)) ;

  IF eof THEN
    DO ;
        right5 = (cwtratio5)**(1/0.5);
        atk5  = 1 - right5           ;
        right1 = exp((cwtratio1))    ;
        atk1  = 1 - right1           ;
        OUTPUT ;
    END ;

  RUN;

%MEND atkin ;

/*----------------------------------*/
/*     MACRO: DEFINE QUANTILES      */
/*----------------------------------*/
%MACRO quantile ;

PROC UNIVARIATE DATA=prepkf NOPRINT;
  BY     country ;
  VAR    ey      ;
  WEIGHT wt      ;
  OUTPUT OUT     = tmp
         PCTLPTS = 10 20 50 80 90
         PCTLPRE = dec ;
RUN ;

DATA   &cc.&yy._3 (KEEP=country d9010 d9050 d8020) ;
 MERGE prepkf   (KEEP=country)
       tmp ;

        FORMAT d9010 d9050 d8020 6.3 ;

        d9010 = dec90 / dec10 ;
        d9050 = dec90 / dec50 ;
        d8020 = dec80 / dec20 ;
        IF _N_ = 1 ;
RUN;

%MEND quantile ;


/*-----------------------------------------*/
/* MACRO: CALCULATE RELATIVE POVERTY RATES */
/*-----------------------------------------*/
%MACRO poverty ;

/***** TOTAL POPULATION *****/
PROC MEANS DATA=prepkf MEAN NOPRINT;
 BY country  ;
 VAR poor4 poor5 poor6;
 WEIGHT wt ;
 OUTPUT OUT=&cc.&yy._4 MEAN=poorall4 poorall5 poorall6 ;
RUN;
DATA   &cc.&yy._4 (KEEP=country poorall4 poorall5 poorall6) ;
 MERGE start      (KEEP=country)
       &cc.&yy._4
       ;
   FORMAT poorall4 poorall5 poorall6 6.3 ;
   poorall4 = poorall4 * 100;
   poorall5 = poorall5 * 100;
   poorall6 = poorall6 * 100;

   IF _N_ = 1 ;
RUN ;

/*****  CHILDREN *****/
PROC MEANS DATA=prepkf MEAN NOPRINT;
 BY country  ;
 VAR poor4 poor5 poor6;
 WEIGHT ct ;
 OUTPUT OUT=&cc.&yy._5 MEAN=poork4 poork5 poork6 ;
RUN;
DATA   &cc.&yy._5 (KEEP=country poork4 poork5 poork6) ;
 MERGE start       (KEEP=country)
       &cc.&yy._5
       ;
   FORMAT poork4 poork5 poork6 6.3 ;
   poork4 = poork4 * 100;
   poork5 = poork5 * 100;
   poork6 = poork6 * 100;

   IF _N_ = 1 ;
RUN ;

/*****  ELDERLY *****/
PROC MEANS DATA=prepkf MEAN NOPRINT;
 BY country  ;
 VAR poor4 poor5 poor6;
 WEIGHT et ;
 OUTPUT OUT=&cc.&yy._6 MEAN=poore4 poore5 poore6 ;
RUN;
DATA   &cc.&yy._6 (KEEP=country poore4 poore5 poore6) ;
 MERGE start       (KEEP=country)
       &cc.&yy._6
       ;
   FORMAT poore4 poore5 poore6 6.3 ;
   poore4 = poore4 * 100;
   poore5 = poore5 * 100;
   poore6 = poore6 * 100;

   IF _N_ = 1 ;
RUN ;

/***** DIST CHILDREN *****/
PROC MEANS DATA=prepkf MEAN NOPRINT;
 BY country  ;
 VAR poor57 poor715 poor15;
 WEIGHT ct ;
 OUTPUT OUT=&cc.&yy._7 MEAN=d5075 d75150 d150 ;
RUN;
DATA   &cc.&yy._7  (KEEP=country d5075 d75150 d150) ;
 MERGE start       (KEEP=country)
       &cc.&yy._7
       ;
   FORMAT d5075 d75150 d150 6.3 ;
   d5075  = d5075  * 100;
   d75150 = d75150 * 100;
   d150   = d150   * 100;

   IF _N_ = 1 ;
RUN ;

/***** POVERTY RATES BY CHIDREN FAMILY TYPE *****/
* Two parents family ;
DATA twoParen ;
 SET prepkf ;
    IF ((married > 0) AND ((d4-d27) > 1)) ;
RUN;

PROC MEANS DATA=twoParen MEAN NOPRINT ;
 BY country  ;
 VAR poor5 ;
 WEIGHT ct ;
 OUTPUT OUT=&cc.&yy._8 MEAN=poortp ;
RUN;

DATA   &cc.&yy._8 (KEEP=country poortp) ;
 MERGE start       (KEEP=country)
       &cc.&yy._8
       ;
   FORMAT    poortp 6.3 ;
   poortp  = poortp  * 100;
   IF _N_ = 1 ;
RUN ;

* Single-mother family ;
DATA singMoth ;
 SET prepkf ;
     IF ( (d27>0) AND ((d3=2) AND (married=0))) ;
RUN;

PROC MEANS DATA=singMoth MEAN NOPRINT ;
 BY country  ;
 VAR poor5 ;
 WEIGHT ct ;
 OUTPUT OUT=&cc.&yy._9 MEAN=poorsm ;
RUN;

DATA   &cc.&yy._9 (KEEP=country poorsm) ;
 MERGE start       (KEEP=country)
       &cc.&yy._9
       ;
   FORMAT    poorsm 6.3  ;
   poorsm  = poorsm * 100;
   IF _N_ = 1 ;
RUN ;

* % of children living in a single-mother family ;
PROC MEANS DATA=prepkf MEAN NOPRINT;
 BY country  ;
 VAR kidsm ;
 WEIGHT ct ;
 OUTPUT OUT=&cc.&yy._10 MEAN=pkidsm ;
RUN;

DATA   &cc.&yy._10 (KEEP=country pkidsm) ;
 MERGE start       (KEEP=country)
       &cc.&yy._10
       ;
   FORMAT    pkidsm 6.3  ;
   pkidsm  = pkidsm * 100;
   IF _N_ = 1 ;
RUN ;

%MEND poverty;

/*------------------------*/
/* MACRO DISPLAY RESULTS  */
/*------------------------*/
%MACRO show ;

* Compile result in a temporary SAS dataset ;
DATA   &cc.&yy._kf  ;
 MERGE
	%DO i = 1 %TO 10 ;
				&cc.&yy._&i
	%END ;
	                  ;
    BY country ;

    ATTRIB
       gini     label='Gini Coefficient       ' format=10.4
       atk5     label='Atkinson (epsilon=0.5) ' format=10.4
       atk1     label='Atkinson (epsilon=1.0) ' format=10.4
       d9010    label='Percentile ratio(90/10)' format=10.4
       d9050    label='Percentile ratio(90/50)' format=10.4
       d8020    label='Percentile ratio(80/20)' format=10.4
       PoorAll4 label='Relative Poverty Rates - Total Population (40%)' format=10.4
       PoorAll5 label='Relative Poverty Rates - Total Population (50%)' format=10.4
       PoorAll6 label='Relative Poverty Rates - Total Population (60%)' format=10.4
       PoorK4   label='Relative Poverty Rates - Children (40%)' format=10.4
       PoorK5   label='Relative Poverty Rates - Children (50%)' format=10.4
       PoorK6   label='Relative Poverty Rates - Children (60%)' format=10.4
       PoorE4   label='Relative Poverty Rates - Elderly (40%)' format=10.4
       PoorE5   label='Relative Poverty Rates - Elderly (50%)' format=10.4 
       PoorE6   label='Relative Poverty Rates - Elderly (60%)' format=10.4
       D5075    label='Distribution of Children by income group (50-75%)' format=10.4
       D75150   label='Distribution of Children by income group (75-150%)' format=10.4
       D150     label='Distribution of Children by income group (above 150%)' format=10.4
       Poortp   label='Children Poverty Rates - Two Parents Family (50%)' format=10.4
       Poorsm   label='Children Poverty Rates - Single Mother Family (50%)' format=10.4
       Pkidsm   label='% Children living in Single Mother Family' format=10.4
    ;  

RUN ;

* Create the outputs ;
%LET dataset= &cc.&yy._KF ;
%LET formgen  = 15.4                    ;
%LET bordure  = '|----|+|---+'          ;
%LET classe   = country                 ;
%LET variable = gini atk5 atk1          ;
%LET table    = country, gini atk5 atk1 ;
%LET box      = 'Gini & Atkinson index' ;
%LET rts      = 40                      ;
%LET lab      = sum = ' '               ;
%TABULATE

%LET dataset= &cc.&yy._KF                  ;
%LET formgen  = 15.4                       ;
%LET bordure  = '|----|+|---+'             ;
%LET classe   = country                    ;
%LET variable = d9010 d9050 d8020          ;
%LET table    = country, d9010 d9050 d8020 ;
%LET box      = 'Percentile ratios'        ;
%LET rts      = 40                         ;
%LET lab      = sum = ' '                  ;
%TABULATE

%LET dataset= &cc.&yy._KF                  ;
%LET formgen  = 15.4                       ;
%LET bordure  = '|----|+|---+'             ;
%LET classe   = country                    ;
%LET variable = poorAll4 poorAll5 poorAll6           ;
%LET table    = country, poorAll4 poorAll5 poorAll6  ;
%LET box      = 'Relative Poverty Rates (Total)'     ;
%LET rts      = 40                         ;
%LET lab      = sum = ' '                  ;
%TABULATE

%LET dataset= &cc.&yy._KF                  ;
%LET formgen  = 15.4                       ;
%LET bordure  = '|----|+|---+'             ;
%LET classe   = country                    ;
%LET variable = poork4 poork5 poork6               ;
%LET table    = country, poork4 poork5 poork6      ;
%LET box      = 'Relative Poverty Rates (Children)';
%LET rts      = 40                         ;
%LET lab      = sum = ' '                  ;
%TABULATE

%LET dataset= &cc.&yy._KF                  ;
%LET formgen  = 15.4                       ;
%LET bordure  = '|----|+|---+'             ;
%LET classe   = country                    ;
%LET variable = poore4 poore5 poore6              ;
%LET table    = country, poore4 poore5 poore6     ;
%LET box      = 'Relative PovertyRates (Elderly)' ;
%LET rts      = 40                         ;
%LET lab      = sum = ' '                  ;
%TABULATE

%LET dataset= &cc.&yy._KF                  ;
%LET formgen  = 15.4                       ;
%LET bordure  = '|----|+|---+'             ;
%LET classe   = country                    ;
%LET variable = d5075 d75150 d150          ;
%LET table    = country, d5075 d75150 d150 ;
%LET box      = 'Distribution of Children Living in Different Income Household '   ;
%LET rts      = 40                         ;
%LET lab      = sum = ' '                  ;
%TABULATE

%LET dataset= &cc.&yy._KF                  ;
%LET formgen  = 15.4                       ;
%LET bordure  = '|----|+|---+'             ;
%LET classe   = country                    ;
%LET variable = poortp poorsm pkidsm       ;
%LET table    = country, poortp poorsm pkidsm ;
%LET box      = 'Poverty Rates for Children by Family Type'   ;
%LET rts      = 40                         ;
%LET lab      = sum = ' '                  ;
%TABULATE

%MEND show ;


/*-----------------------*/
/* MACRO: PROC TABULATE  */
/*-----------------------*/
%MACRO tabulate ;

       PROC TABULATE DATA=&dataset
                     FORMAT=&formgen
                     FORMCHAR=&bordure
                     NOSEPS
                     ;

            CLASS &classe ;

            VAR &variable ;

            TABLE &table / CONDENSE
                           PRINTMISS
                           BOX="&box"
                           RTS=&rts
                           ;

            KEYLABEL &lab ;

       RUN ;

%MEND tabulate ;


/*************************************************************************/
/**               PART II: RUN THE KEY FIGURE PROGRAM                   **/
/*************************************************************************/

/*----------------------------------*/
/*  JOB SUBMISSION: DEFINE DATASET  */
/*----------------------------------*/
%LET cc = your-cc ;
%LET yy = your-yy ;
%PREP
%GINI
%ATKIN
%QUANTILE
%POVERTY
%SHOW