* This SAS program links the 1989 Pension Provider Survey data to the 1989
  Survey of Consumer Finances cross-sectional and panel datasets.
  Users are warned not to combine the two datasets, since the 1989 SCF
  Cross-section was imputed independently from the 1989 SCF Panel.
  Cross-sectional cases have been imputed 5 times, while Panel cases
  have been imputed 3 times.
  The number of observations obtained when the 1989 SPP and the 1989
  SCF Cross-section are linked is 5,645.
  The number of observations obtained when the 1989 SPP and the 1989
  SCF Panel are linked is 651.;


LIBNAME SCF 'scf dataset library';
LIBNAME OUT 'output dataset library';
FILENAME LINKPAN 'ASCII flatfile spp89merge.panel.txt';
FILENAME LINKXS 'ASCII flatfile spp89merge.xs.txt';
FILENAME PENDAT 'ASCII flatfile of main data, spp89data.zip' LRECL=19249 ;


************* Code to link the 1989 SPP to the 1989 SCF Cross-section;

* Read in the file that links pension plans to 1989 SCF
  cross-sectional cases;
DATA LINKXS;
  INFILE LINKXS;
  INPUT @9 PENPLAN 4. @17 HHID 5. ;
  XX1 = INT(HHID/10);
RUN;

* Sort data by plan number for merging;
PROC SORT DATA=LINKXS;
  BY PENPLAN;
RUN;

PROC PRINT DATA=LINKXS;
RUN;

* Read in main pension provider data, the variables listed are
  necessary for linking the two datasets, users can add any other
  desired variables from the pension provider file;
DATA PENDAT;
  INFILE PENDAT MISSOVER;
  INPUT @1 CODEID 6. @7 SEQNUM 4. @11 PPID 4. @15 PLAN 3. @18 ATTACH 1.;
  PENPLAN=CODEID;
RUN;

* Sort data by plan number for merging;
PROC SORT DATA=PENDAT;
  BY PENPLAN;
RUN;

PROC PRINT DATA=PENDAT;
RUN;

* Merge the link data (linkxs) and the main pension provider data
  (pendat) by the plan number;
DATA ALLPENXS;
  MERGE LINKXS PENDAT;
  BY PENPLAN;
RUN;

* Sort the merged pension data by the SCF household id number;
PROC SORT DATA=ALLPENXS;
  BY XX1;
RUN;

PROC PRINT DATA=ALLPENXS;
RUN;

* Read in the 1989 SCF cross-sectional data;
DATA SCF89XS;

  SET SCF.1989 SCF cross-sectional dataset;
  
* If the case was swapped (for an explanation of swapping, please see
  the 1989 codebook), need to reswap data to match the pension
  provider data.  The variables listed below are the pension variables
  for the respondent and the spouse/partner. 
  NOTE: Users may want to include other work status/history variables
  in the reswapping;
  IF X8000=1 THEN DO;
    ARRAY HEAD {*} X4735-X4741 X4801-X4814 X4902-X4914 
                   X5002-X5014 X4816-X4834 X4916-X4934 X5016-X5034
	           X5036 X5037;

    ARRAY SPP {*} X4135-X4141 X4201-X4214 X4302-X4314 
                  X4402-X4414 X4216-X4234 X4316-X4334 X4416-X4434
                  X4436 X4437;

     DO I=1 TO DIM(HEAD);
        TEMP1=HEAD{I};
        TEMP2=SPP{I};
        HEAD{I}=TEMP2;
        SPP{I}=TEMP1;
     END;
  END;
 
RUN;

* Sort the 1989 SCF cross-sectional data by household id;
PROC SORT DATA=SCF89XS;
  BY XX1;
RUN;

* Merge the pension provider data with the 1989 SCF cross-sectional
  data by household id;
* Note the use of proc sql, this is needed for an unequal many to
  many merge (note: a warning message will be produced);
PROC SQL;
  CREATE TABLE OUT.PEN89XS AS
  SELECT *
    FROM ALLPENXS INNER JOIN SCF89XS
    ON ALLPENXS.XX1 = SCF89XS.XX1;
  QUIT;
RUN;

PROC PRINT DATA=OUT.PEN89XS;
RUN;

************* Code to link the 1989 SPP to the 1989 SCF Panel;

* Read in the file that links pension plans to 1989 SCF
  panel cases;
DATA LINKPAN;
  INFILE LINKPAN;
  INPUT @9 PENPLAN 4. @17 HHID 5. ;
  XX1 = INT(HHID/10);
RUN;

* Sort data by plan number for merging;
PROC SORT DATA=LINKPAN;
  BY PENPLAN;
RUN;

PROC PRINT DATA=LINKPAN;
RUN;

* Read in main pension provider data, the variables listed are
  necessary for linking the two datasets, users can add any other
  desired variables from the pension provider file;
DATA PENDAT;
  INFILE PENDAT MISSOVER;
  INPUT @1 CODEID 6. @7 SEQNUM 4. @11 PPID 4. @15 PLAN 3. @18 ATTACH 1.;
  PENPLAN=CODEID;
RUN;

* Sort data by plan number for merging;
PROC SORT DATA=PENDAT;
  BY PENPLAN;
RUN;

PROC PRINT DATA=PENDAT;
RUN;

* Merge the link data (linkpan) and the main pension provider data
  (pendat) by the plan number;
DATA ALLPENP;
  MERGE LINKPAN PENDAT;
  BY PENPLAN;
RUN;

* Sort the merged pension data by the SCF household id number;
PROC SORT DATA=ALLPENP;
  BY XX1;
RUN;

PROC PRINT DATA=ALLPENP;
RUN;

* Read in the 1989 SCF panel data;
DATA SCF89P;

  SET SCF.1989 SCF Panel dataset;
  
* If the case was swapped (for an explanation of swapping, please see
  the 1989 panel codebook), need to reswap data to match the pension
  provider data.  The variables listed below are the pension variables
  for the respondent and the spouse/partner. 
  NOTE: Users may want to include other work status/history variables
  in the reswapping;  
  IF X8000=1 THEN DO;
    ARRAY HEAD {*} X4735-X4741 X4801-X4814 X4902-X4914 
                   X5002-X5014 X4816-X4834 X4916-X4934 X5016-X5034
                   X5036 X5037;

    ARRAY SPP {*} X4135-X4141 X4201-X4214 X4302-X4314 
                  X4402-X4414 X4216-X4234 X4316-X4334 X4416-X4434
                  X4436 X4437;

     DO I=1 TO DIM(HEAD);
        TEMP1=HEAD{I};
        TEMP2=SPP{I};
        HEAD{I}=TEMP2;
        SPP{I}=TEMP1;
     END;
  END;
 
RUN;

* Sort the 1989 SCF panel data by household id;
PROC SORT DATA=SCF89P;
  BY XX1;
RUN;

* Merge the pension provider data with the 1989 SCF panel
  data by household id;
* Note the use of proc sql, this is needed for an unequal many to
  many merge (note: a warning message will be produced);
PROC SQL;
  CREATE TABLE OUT.PEN89P AS
  SELECT *
    FROM ALLPENP INNER JOIN SCF89P
    ON ALLPENP.XX1 = SCF89P.XX1;
  QUIT;
RUN;

PROC PRINT DATA=OUT.PEN89P;
RUN;

ENDSAS;