options pagesize=3355 linesize=78 compress=binary nocenter; %let dirname = C:\AHS_11\NEW_AHS_11_V3\; filename newfile "&dirname\hud.xpt"; libname ahsraw "&dirname"; * Note: Lines above are where the input and output files are located.; %LET dset = AHS2011_V3; * name of AHS dataset; * Revised for 2011; *The contents of the original files (input files) and the flat file AHS2011.AHS2011 (output file) are written to the following Excel file "Contents_AHS2011_V2; *********************************************************************************************************; ods HTML file="&dirname\contents_AHS2011_V3.XLS"; **********************************************************************************************************; /* * Name: In_ahs2012_with_Upper_case_09-21-2012.sas Date: 8-21-2012 This program is designed to convert the SAS transport files provided by the Census Bureau into a single SAS dataset with a similar structure to pre-1997 data. The program was developed by the Housing and Community Development Group, part of ICF Consulting, Inc., and revised for 2011 by Econometirca Inc. under contract with HUD. For questions about the SAS program, please contact: Fred Eggers (FJEggers@aol.com) or Fouad Moumen (fmoumen@aol.com Tel 703 801 8188) Econometrica Inc. Questions to HUD about the AHS should be directed to one of the following: Dav Vandenbrouke 202-402-5890 david_a._vandenbroucke@hud.gov Carolyn Lynch 202-402-5910 carolyn.lynch@hud.gov Questions to Census about the AHS should be directed to either: Tamara Cole (email: tamara.a.cole@census.gov) or Joe Huesman (email: jhuesman@census.gov). They can both be reached by phone at: 301-763-3235. */ * First extract the individual files from the SAS transport file; proc cimport library=ahsraw infile=newfile; run; * End of reading from the raw files; * Define a macro to show the contents of each individual file, and set a flag.; %macro runcim(filen); proc contents data=ahsraw.&filen; title "&filen file"; run; proc sort data=ahsraw.&filen; by control; run; %mend; /* * Run the macro for each individual file; %runcim(owner); %runcim(homimp); %runcim(mortg); %runcim(rmov); %runcim(person); %runcim(newhouse); %runcim(ratiov); */ * Set up the individual data to handle multiple observations for the same household; * Multiple observations in the person file.; data ahsraw.person; set ahsraw.person; if rel=1 or rel=2 then hhead=1; else hhead=0; movgrp=mvg; famgrp=mvg; run; * Lines above also prepare for other merging.; proc sort data=ahsraw.person; by control; run; proc means noprint data=ahsraw.person; var age; by control; output out=junk n=numfam; run; data junk; set junk; keep control numfam; run; data ahsraw.person; merge ahsraw.person junk; by control; run; * Merge on JTW here; proc sort data=ahsraw.person; by control pline; run; * Merge in Recent Movers here; proc sort data=ahsraw.person; by control movgrp; run; data ahsraw.rmov; set ahsraw.rmov; movgrp=mvg; run; proc sort data=ahsraw.rmov; by control movgrp; run; data ahsraw.person; merge ahsraw.person ahsraw.rmov; by control movgrp; run; proc sort data=ahsraw.person; by control hhead descending pline; run; * Multiple observations in the Home Improvement file; proc sort data=ahsraw.homimp; by control descending ras; run; data ahsraw.homimp; set ahsraw.homimp; racost=rad; rastemp=ras+0; run; proc means noprint data=ahsraw.homimp; var rastemp; by control; output out=junk1 n=numfam; run; data junk1; set junk1; keep control numfam; run; data ahsraw.homimp; merge ahsraw.homimp junk1; by control; run; * Define a macro to handle the change in format to be similar to the old style where there is a single household record as opposed to individual records; %macro lagvar(newvar,origvar,arvar); &newvar.1=&origvar; &newvar.2=lag1(&origvar); &newvar.3=lag2(&origvar); &newvar.4=lag3(&origvar); &newvar.5=lag4(&origvar); &newvar.6=lag5(&origvar); &newvar.7=lag6(&origvar); &newvar.8=lag7(&origvar); &newvar.9=lag8(&origvar); &newvar.10=lag9(&origvar); &newvar.11=lag10(&origvar); &newvar.12=lag11(&origvar); &newvar.13=lag12(&origvar); &newvar.14=lag13(&origvar); &newvar.15=lag14(&origvar); &newvar.16=lag15(&origvar); array &arvar {*} &newvar.1 &newvar.2-&newvar.16; do x=1 to 16; if x>numfam then &arvar(x)=.; end; drop x &origvar; %mend; * Now set up code to do transformation to single record from multiple record.; * Transform person file; data ahsraw.person; set ahsraw.person; by control hhead descending pline; /***** from the person file *****/ %lagvar(age,age,tempa); %lagvar(citshp,citshp,tempna); %lagvar(famnum,famnum,tempfa); %lagvar(famrel,famrel,tempfb); %lagvar(famtyp,famtyp,tempfc); %lagvar(food,food,tempm); %lagvar(grad,grad,tempb); %lagvar(inusyr,inusyr,tempnb); %lagvar(jage,jage,tempog); %lagvar(jatvty,jatvty,tempoh); %lagvar(jgrad,jgrad,tempoi); %lagvar(jitshp,jitshp,tempok); %lagvar(jmar,jmar,tempol); %lagvar(jmove,jmove,tempom); %lagvar(jmovm,jmovm,tempon); %lagvar(jmvg,jmvg,tempoo); %lagvar(jnusyr,jnusyr,tempop); %lagvar(jpar,jpar,tempoq); %lagvar(jpqalim,jpqalim,tempo1); %lagvar(jpqdiv,jpqdiv,tempo2); %lagvar(jpqint,jpqint,tempo3); %lagvar(jpqother,jpqother,tempot1); %lagvar(jpqothnr,jpqothnr,tempot); %lagvar(jpqrent,jpqrent,tempo11); %lagvar(jpqretir,jpqretir,tempo12); %lagvar(jpqsal,jpqsal,tempov); %lagvar(jpqsalnr,jpqsalnr,tempox); %lagvar(jpqself,jpqself,tempoy); %lagvar(jpqslfnr,jpqslfnr,tempoz); %lagvar(jpqss,jpqss,temppa); %lagvar(jpqssi,jpqssi,temppb); %lagvar(jpqwelf,jpqwelf,temppc); %lagvar(jpqwkcmp,jpqwkcmp,temppd); %lagvar(jpvother,jpvother,tempp1); %lagvar(jrace,jrace,temppf); %lagvar(jrel,jrel,temppg); %lagvar(jsal,jsal,tempph); %lagvar(jsex,jsex,temppi); %lagvar(jspan,jspan,temppj); %lagvar(jspos,jspos,temppk); %lagvar(jten,jten,tempp11); %lagvar(lodrnt,lodrnt,tempz1); %lagvar(lodsta,lodsta,tempz); %lagvar(mar,mar,tempt); %lagvar(move,move,tempc); %lagvar(movm,movm,tempdz); %lagvar(mvg,mvg,tempnd); %lagvar(natvty,natvty,tempnc); %lagvar(par,par,tempe); %lagvar(PASTHWHO,PASTHWHO,tempnZj); %lagvar(PBTUB,PBTUB,tempnZj1); %lagvar(pcare,pcare,tempzx); %lagvar(PCTRUSE,PCTRUSE,tempnZj2); %lagvar(perrnd,perrnd,tempzy); %lagvar(PFAUCET,PFAUCET,tempnZj3); %lagvar(PGETBR,PGETBR,tempnZj4); %lagvar(PGRASP,PGRASP,tempnZj5); %lagvar(phear,phear,tempzt); %lagvar(PKCAB,PKCAB,tempnZj6); %lagvar(pline,pline,tempf); %lagvar(PMEMRY,PMEMRY,tempnZk); %lagvar(POCAB,POCAB,tempnZl); %lagvar(PQALIM,PQALIM,tempnZa); %lagvar(PQDIV,PQDIV,tempnZb); %lagvar(PQINT,PQINT,tempnZc); %lagvar(PQOTHER,PQOTHER,tempnZd); %lagvar(pqothnr,pqothnr,tempng); %lagvar(PQRENT,PQRENT,tempnZe); %lagvar(pqretir,pqretir,tempnh); %lagvar(pqsal,pqsal,tempni); %lagvar(pqsalnr,pqsalnr,tempnj); %lagvar(pqself,pqself,tempnk); %lagvar(pqselfnr,pqselfnr,tempnl); %lagvar(pqss,pqss,tempnm); %lagvar(pqssi,pqssi,tempnn); %lagvar(pqwelf,pqwelf,tempno); %lagvar(pqwkcmp,pqwkcmp,tempnp); %lagvar(PREACH,PREACH,tempnZi); %lagvar(psee,psee,tempzu); %lagvar(PSINK,PSINK,tempnZf); %lagvar(PSTOOP,PSTOOP,tempnZg); %lagvar(PSTOV,PSTOV,tempnZh); %lagvar(pvother,pvother,tempnq); %lagvar(pwalk,pwalk,tempzw); %lagvar(PWSHWR,PWSHWR,tempnZe1); %lagvar(race,race,tempv); %lagvar(rel,rel,tempg); %lagvar(rntdue,rntdue,temph); %lagvar(sal,sal,tempi); %lagvar(sex,sex,tempw); %lagvar(span,span,tempx); %lagvar(spos,spos,tempj); %lagvar(ten,ten,tempy); /*** from the rmov file ****/ %lagvar(jovgrp,jovgrp,tempo8); %lagvar(jxhead,jxhead,tempoc); %lagvar(jxper,jxper,tempod); %lagvar(jxten,jxten,tempoe); %lagvar(jxunit,jxunit,tempof); %lagvar(mvg,mvg,tempof2); %lagvar(XCOND,XCOND,tempa1); %lagvar(xcoop,xcoop,tempav); %lagvar(xcost,xcost,tempaw); %lagvar(xhead,xhead,tempax); %lagvar(xinus,xinus,tempaz1); %lagvar(xper,xper,tempaz); %lagvar(xrel,xrel,tempba); %lagvar(xten,xten,tempbb); %lagvar(xunit,xunit,tempbc); if last.control=1 then output; drop hhead; run; * Transform home improvement file; data ahsraw.homimp; set ahsraw.homimp; by control descending ras; %lagvar(rad,rad,tempaa); %lagvar(rah,rah,tempab); %lagvar(ras,ras,tempac); %lagvar(jras,jras,tempng); %lagvar(jrad,jrad,tempaaj); %lagvar(rahk,rahk,tempaahk); if last.control=1 then output; run; proc sort data=AHSRAW.OWNER; by control; run; proc sort data=AHSRAW.homimp; by control; run; proc sort data=ahsraw.mortg; by control; run; proc sort data=ahsraw.person; by control; run; proc sort data=ahsraw.newhouse; by control; run; proc sort data=ahsraw.ratiov; by control; run; * Merge all files together; data ahsraw.&dset; merge ahsraw.owner ahsraw.homimp ahsraw.mortg ahsraw.person ahsraw.newhouse ahsraw.ratiov; by control; /*drop ownerf homimpf mortgf rmovf personf newhousef ratiovf rastemp numfam;*/ drop MOVGRP; run; /* proc datasets ddname=ahsraw; delete owner homimp jtw mortg person rmov newhouse ratiov; run; */ * Code to switch to upper case is thanks to Dav Vandenbroucke; FILENAME OutFile1 "&dirname.\ReName.txt" LRECL=32767; /* write content listing to a dataset */ PROC CONTENTS DATA=AHSraw.&dset. OUT=Content NOPRINT; RUN; /* write format statements to text file */ DATA _NULL_ ; FILE OutFile1; SET Content; UName = UPCASE(Name); IF UName ^= Name THEN PUT 'RENAME ' Name $ '=' UName +(-1) '; ' ; RUN; /* Assign the formats to the AHS dataset */ PROC DATASETS DDNAME=AHSraw ; MODIFY &dset ; %INCLUDE OutFile1; /* inserts rename statements created in previous step */ RUN; **** ADDING LABELS TO THE ADDED VARIABLES "LAG VARIABLES" *****; *** The following Macro is used to set label statements saved in zlbl.txt ***; FILENAME OutFile2 "&dirname.\zlbl.txt" LRECL=32767; * Note: Line above is where the temporary output file zlbl.txt is located, which will be deleted at the end of this program; %macro labelgen (varnme=,lbl=); data _null_; file outfile2 mod; %let var=&varnme.; %do i = 1 %to 16; %let indx = &i.; %let varname = &var&i.; varlbl = &lbl. || ": Person&indx. "; put "label &varname. = " '"' varlbl @; put +(-1) '";'; %end; run; %mend labelgen; %labelgen(varnme=AGE,lbl="Age"); %labelgen(varnme=CITSHP,lbl="US Citizenship"); %labelgen(varnme=FAMNUM,lbl="Family number"); %labelgen(varnme=FAMREL,lbl="Family relationship"); %labelgen(varnme=FAMTYP,lbl="Family type"); %labelgen(varnme=FOOD,lbl="Meals included in rent paid to household"); %labelgen(varnme=GRAD,lbl="Educational level of person"); %labelgen(varnme=INUSYR,lbl="Year came to US"); %labelgen(varnme=JAGE,lbl="Edit flag for AGE"); %labelgen(varnme=JATVTY,lbl="Edit flag for NATVTY"); %labelgen(varnme=JGRAD,lbl="Edit flag for GRAD"); %labelgen(varnme=JITSHP,lbl="Edit flag for CITSHP"); %labelgen(varnme=JMAR,lbl="Edit flag for MAR"); %labelgen(varnme=JMOVE,lbl="Edit flag for MOVE"); %labelgen(varnme=JMOVM,lbl="Edit flag for MOVM"); %labelgen(varnme=JMVG,lbl="Edit flag for MVG"); %labelgen(varnme=JNUSYR,lbl="Edit flag for INUSYR"); %labelgen(varnme=JOVGRP,lbl="Edit flag for MOVGRP"); %labelgen(varnme=JPAR,lbl="Edit flag for PAR"); %labelgen(varnme=JPQALIM,lbl="Edit flag for PQALIM"); %labelgen(varnme=JPQDIV,lbl="Edit flag for PQDIV"); %labelgen(varnme=JPQINT,lbl="Edit flag for PQINT"); %labelgen(varnme=JPQOTHER,lbl="Edit flag for PQOTHER"); %labelgen(varnme=JPQOTHNR,lbl="Edit flag for PQOTHNR"); %labelgen(varnme=JPQRETIR,lbl="Edit flag for PQRETIR"); %labelgen(varnme=JPQRETIR,lbl="Edit flag for PQRETIR"); %labelgen(varnme=JPQSAL,lbl="Edit flag for PQSAL"); %labelgen(varnme=JPQSALNR,lbl="Edit flag for PQSALNR"); %labelgen(varnme=JPQSELF,lbl="Edit flag for PQSELF"); %labelgen(varnme=JPQSLFNR,lbl="Edit flag for PQSELFNR"); %labelgen(varnme=JPQSS,lbl="Edit flag for PQSS"); %labelgen(varnme=JPQSSI,lbl="Edit flag for PQSSI"); %labelgen(varnme=JPQWELF,lbl="Edit flag for PQWELF"); %labelgen(varnme=JPQWKCMP,lbl="Edit flag for PQWKCMP"); %labelgen(varnme=JPVOTHER,lbl="Edit flag for PVOTHER"); %labelgen(varnme=JRACE,lbl="Edit flag for RACE"); %labelgen(varnme=JREL,lbl="Edit flag for REL"); %labelgen(varnme=JSAL,lbl="Edit flag for SAL"); %labelgen(varnme=JSEX,lbl="Edit flag for SEX"); %labelgen(varnme=JSPAN,lbl="Edit flag for SPAN"); %labelgen(varnme=JSPOS,lbl="Edit flag for SPOS"); %labelgen(varnme=JTEN,lbl="Edit flag for TEN"); %labelgen(varnme=JXHEAD,lbl="Edit flag for XHEAD"); %labelgen(varnme=JXPER,lbl="Edit flag for XPER"); %labelgen(varnme=JXTEN,lbl="Edit flag for XTEN"); %labelgen(varnme=JXUNIT,lbl="Edit flag for XUNIT"); %labelgen(varnme=LODRNT,lbl="Amount lodger pays to hhld"); %labelgen(varnme=LODSTA,lbl="Lodger pays fixed amount to hhld"); %labelgen(varnme=MAR,lbl="Marital status of person"); %labelgen(varnme=MOVE,lbl="Year person moved in"); %labelgen(varnme=MOVM,lbl="Month person moved in"); %labelgen(varnme=MVG,lbl="Recent mover group"); %labelgen(varnme=NATVTY,lbl="Country of birth"); %labelgen(varnme=PAR,lbl="Line number of parent of person"); %labelgen(varnme=PASTHWHO,lbl="Has asthma"); %labelgen(varnme=PBTUB,lbl="Has difficulty getting into/out of bathtub"); %labelgen(varnme=PCARE,lbl="Person has difficulty dressing or bathing"); %labelgen(varnme=PCTRUSE,lbl="Has difficulty using kitchen counters"); %labelgen(varnme=PERRND,lbl="Person has difficulty doing errands"); %labelgen(varnme=PFAUCET,lbl="Has difficulty using faucets"); %labelgen(varnme=PGETBR,lbl="Has difficulty getting to bathroom"); %labelgen(varnme=PGRASP,lbl="Has difficulty using fingers to grasp small object"); %labelgen(varnme=PHEAR,lbl="Person has difficulty hearing"); %labelgen(varnme=PKCAB,lbl="Has difficulty reaching kitchen cabinets"); %labelgen(varnme=PLINE,lbl="Line number of person"); %labelgen(varnme=PMEMRY,lbl="Person has difficulty concentrating or remembering"); %labelgen(varnme=POCAB,lbl="Has difficulty opening kitchen cabinets"); %labelgen(varnme=PQALIM,lbl="Person has alimony or child support income"); %labelgen(varnme=PQDIV,lbl="Person has dividends"); %labelgen(varnme=PQINT,lbl="Person has interest income"); %labelgen(varnme=PQOTHER,lbl="Person has unempl/workers comp/VA/other pay"); %labelgen(varnme=PQOTHNR,lbl="Non-relative other income received on a regular basis"); %labelgen(varnme=PQRENT,lbl="Person has rental income"); %labelgen(varnme=PQRETIR,lbl="Person has retirement or survivor pension"); %labelgen(varnme=PQSAL,lbl="Person has wages and salary"); %labelgen(varnme=PQSALNR,lbl="Non-relative wages and salary"); %labelgen(varnme=PQSELF,lbl="Person received self employment income"); %labelgen(varnme=PQSELFNR,lbl="Non-relative self employment income"); %labelgen(varnme=PQSS,lbl="Person has social security income"); %labelgen(varnme=PQSSI,lbl="Person has supplemental security income"); %labelgen(varnme=PQWELF,lbl="Person received public assistance"); %labelgen(varnme=PQWKCMP,lbl="Person received disability or workers comp"); %labelgen(varnme=PREACH,lbl="Has difficulty reaching above head"); %labelgen(varnme=PSEE,lbl="Person is has difficulty seeing"); %labelgen(varnme=PSINK,lbl="Has difficulty using sink"); %labelgen(varnme=PSTOOP,lbl="Has difficulty stooping/kneeling/bending"); %labelgen(varnme=PSTOV,lbl="Has difficulty using stove"); %labelgen(varnme=PVOTHER,lbl="Individual's non-wage income"); %labelgen(varnme=PWALK,lbl="Person has difficulty walking or climbing stairs"); %labelgen(varnme=PWSHWR,lbl="Has difficulty getting into/out of walk-in shower"); %labelgen(varnme=RACE,lbl="Race of person"); %labelgen(varnme=REL,lbl="Relatnship of person to reference person"); %labelgen(varnme=RNTDUE,lbl="Frequency of lodger's rent pymnt to hhld"); %labelgen(varnme=SAL,lbl="Wage & salary income of person"); %labelgen(varnme=SEX,lbl="Sex of person"); %labelgen(varnme=SPAN,lbl="Spanish origin of person"); %labelgen(varnme=SPOS,lbl="Line number of spouse of person"); %labelgen(varnme=TEN,lbl="This person is owner/renter of unit"); %labelgen(varnme=JOVGRP,lbl="Edit flag for JOVGRP"); %labelgen(varnme=JXHEAD,lbl="Edit flag for XHEAD"); %labelgen(varnme=JXPER,lbl="Edit flag for XPER"); %labelgen(varnme=JXTEN,lbl="Edit flag for XTEN"); %labelgen(varnme=JXUNIT,lbl="Edit flag for XUNIT"); %labelgen(varnme=MVG,lbl="Recent mover group"); %labelgen(varnme=XCOND,lbl="Previous residence was condo/co-op"); %labelgen(varnme=XCOOP,lbl="Previous residence was a cooperative"); %labelgen(varnme=XCOST,lbl="Current housing costs same as old unit"); %labelgen(varnme=XHEAD,lbl="Prev unit ownd/rentd by one who mvd here"); %labelgen(varnme=XINUS,lbl="Previous residence outside united states"); %labelgen(varnme=XPER,lbl="# people living in last unit before move"); %labelgen(varnme=XREL,lbl="Previous unit owner/rented by relative"); %labelgen(varnme=XTEN,lbl="Tenure of previous residence"); %labelgen(varnme=XUNIT,lbl="Type of unit of previous residence"); %labelgen(varnme=RAD,lbl="Cost of alteration/repair"); %labelgen(varnme=RAH,lbl="Hhld member performed alteration/repair"); %labelgen(varnme=RAS,lbl="Type of alteration/repair - 1999 version"); %labelgen(varnme=JRAS,lbl="Edit flag for RAS"); /*** NEW VARIABLES FROM HOMIMP FOR 2011 ***/ %labelgen(varnme=JRAD,lbl="Edit flag for RAD"); %labelgen(varnme=RAHK,lbl="Hurricane Katrina related job"); PROC DATASETS DDNAME=AHSraw; MODIFY &dset; %INCLUDE Outfile2; /* inserts rename statements created in previous step */ RUN; dm "x erase &dirname.\zlbl.txt"; RUN; * End of code to change variable names; PROC CONTENTS DATA=AHSraw.&dset.; title "Contents of AHS 2011 File"; run;