/************************************************************************* Program: LGT Inperson Flags.sas Project: Housing Discrimination Study: Lesbian, Gay and Transgender Author: Rob Pitingolo (originally for HDS-2012) Edited by: Brent Howell Date Created: 05/04/2015 Last Edited: 09/02/2015 Description: Creates flags to restrict rental data for analysis. Output File: rental_flags *************************************************************************/ /***CREATE FILE STRUCTURE***/ /**Supporting Programs**/ /*Change as Needed*/ %include 'E:\hdslgt\datasubmission\programs\macros.sas'; /**Input Files*/ /*Change as needed*/ libname lgtin 'Y:\METRO\HDS-LGT\raw\sas\201521Sept\'; /**Output Files*/ /*Change as needed*/ libname lgtech 'E:\hdslgt\datasubmission\technical\'; /***CREATE INDICATORS FOR FLAGS FROM RENTAL DATA***/ /**Rental Data for Approved Tests**/ proc sql noprint; create table rentalflags_in as select * from lgtin.rental where control in (select control from lgtin.taf where fpass = 1 and FCOMPLT=1) order by control; quit; /**Only In-person Tests**/ data flags_approved; set rentalflags_in; **Recode Class ID & Tester Type**; %protected; %testtype; %fixtests;/*drop two transgender tests that were approved by the g&l track rules but shouldnt be included in the analysis*/ **keep only in-person tests**; if ip = 1; **Indicators for all approved tests**; if protected = 1 then lg_approved = 1; if protected = 0 then c_approved = 1; **Indicators for met an agent**; if rmeetag = 1 then metagent= 1; else metagent = 0; if protected = 1 and metagent = 1 then lg_metagent = 1; if protected = 0 and metagent = 1 then c_metagent = 1; **Indicators for Anyunit**; if protected = 1 and rtotunit>0 then lg_anyunit = 1; if protected = 0 and rtotunit>0 then c_anyunit = 1; **Indicators for No Recommended units; if rtotunit in (0,.) then nounit = 1; else nounit = 0; if protected = 1 and nounit = 1 then lg_nounit = 1; if protected = 0 and nounit = 1 then c_nounit = 1; **indicator for told about any listitem**; if rbrochr or rhmgdevd or rfininfo or rgift or rfoodbev or rpother in (1) then listitem = 1; if protected = 1 and listitem = 1 then lg_listitem = 1; if protected = 0 and listitem = 1 then c_listitem = 1; **indicator for shown_unit**; if protected = 1 and rinspun >0 then lg_shown_unit = 1; if protected = 0 and rinspun >0 then c_shown_unit = 1; run; /**Sort data by control and tester before summary**/ proc sort data = flags_approved; by control testerid; run; /**summarize rental visit intermediate indicators to a singel test record**/ %let rflags1 = lg_approved c_approved lg_metagent c_metagent lg_anyunit c_anyunit lg_nounit c_nounit lg_anyunit c_anyunit lg_listitem c_listitem lg_shown_unit c_shown_unit; proc summary data = flags_approved noprint; class control; var &rflags1.; output out= rentalflags1 (where =(_type_ = 1)) mean =; run; /***END INDICATORS FOR FLAGS FROM RENTAL DATA***/ proc means data = rentalflags1; run; /***BEGIN INDICATORS FOR FLAGS FROM AVAILABLE UNIT DATA***/ /**Load Available Unit Form for Approved Tests**/ proc sql noprint; CREATE TABLE avunit_in AS SELECT * FROM lgtin.availunit WHERE delrec IN (0,.) AND control IN (SELECT control FROM lgtin.taf WHERE fpass=1 and FCOMPLT = 1) ORDER BY CONTROL; quit; /**Available Unit From Cleaning**/ data availunitflags; set avunit_in; **Recode Class ID**; %protected; %testtype2; %testtype; %fixtests; if ip=1; **Indicator for Shown at least one unit**; if rumodl = 1 then shown_unit_avu = 1; else shown_unit_avu = 0; **Type of Unit Inspected**; if ruinsp = 1 then shown_actual = 1; else shown_actual = 0; **Told a Rent Price**; if rurent > 100 then toldrent = 1; else toldrent = 0; **Told security deposit for any unit**; if rudepst in (1, 2) and rudepsts > 5 then sdany = 1; else sdany = 0; **Told Surety bond for any unit**; if rudepst in (1,3) and rudepsts > 5 then sbany = 1; else sbany = 0; **Macro to creat application fees and move in fee indicators**; %macro fees (var); %do k = 1 %to 4; if &var.&k. = 4 then appfee&k. = 1; if &var.&k. = 11 then mvfee&k. = 1; if &var.&k. in (1, 2, 3, 5, 6, 7, 8, 9, 10, 12, 13, 14, 15) then anyothfee&k. = 1; %end; appfee = sum (of appfee1 appfee2 appfee3 appfee4); mvfee = sum (of mvfee1 mvfee2 mvfee3 mvfee4); anyothfee = sum (of anyothfee1 anyothfee2 anyothfee3 anyothfee4); %mend; %fees(nmmfee); if rurent >100 then do; **Application fee for any unit**; if appfee > 0 then anyappfee = 1; else anyappfee = 0; **Move-in Fees for any unit**; if mvfee > 0 then anymvfee = 1; else anymvfee = 0; **Additional Mandatory fees other than application or move in fees for any unit**; if anyothfee > 0 then anyaddmfee = 1; else anyaddmfee = 0; **Incentives for any unit**; if rinctv = 1 then anyrinctv = 1; else anyrinctv = 0; end; run; /*Summarize Tests to Combine Multiple Available Unit Forms Into One Mean of the Record*/ proc summary data = availunitflags noprint; class control testerid protected; var shown_unit_avu shown_actual toldrent sdany sbany anyappfee anymvfee anyaddmfee anyrinctv; output out = avuflags1 (where = (_type_ = 7)) mean =; run; /*Create Indicators by presence of Child*/ data avuflags2; set avuflags1; **Tester Shown At least one unit**; if protected = 0 and shown_unit_avu > 0 then c_shown_unit_avu = 1; if protected = 1 and shown_unit_avu > 0 then lg_shown_unit_avu = 1; **Tester Saw at least one advertised unit**; if protected = 0 and shown_actual > 0 then c_shown_actual = 1; if protected = 1 and shown_actual > 0 then lg_shown_actual = 1; **Tester told about at least one rent**; if protected = 0 and toldrent > 0 then c_toldrent = 1; if protected = 1 and toldrent > 0 then lg_toldrent = 1; **Tester told about at least one security deposit**; if protected = 0 and sdany > 0 then c_sdany = 1; if protected = 1 and sdany > 0 then lg_sdany = 1; **Tester told about at least one surety bond; if protected = 0 and sbany > 0 then c_sbany = 1; if protected = 1 and sbany > 0 then lg_sbany = 1; **Tester told about any application fee**; if protected = 0 and anyappfee > 0 then c_anyappfee = 1; if protected = 1 and anyappfee > 0 then lg_anyappfee = 1; **Tester told about any move-in fee**; if protected = 0 and anymvfee > 0 then c_anymvfee = 1; if protected = 1 and anymvfee > 0 then lg_anymvfee = 1; **Tester told about any additional mandatory fee**; if protected = 0 and anyaddmfee > 0 then c_anyaddmfee = 1; if protected = 1 and anyaddmfee > 0 then lg_anyaddmfee = 1; **Tester told about any incentives**; if protected = 0 and anyrinctv > 0 then c_anyinctv = 1; if protected = 1 and anyrinctv > 0 then lg_anyinctv = 1; run; /*Variable List for Available Unit Form Summary Function*/ %let rflag2 = c_shown_unit_avu lg_shown_unit_avu c_shown_actual lg_shown_actual c_toldrent lg_toldrent c_sdany lg_sdany c_sbany lg_sbany c_anyappfee lg_anyappfee c_anymvfee lg_anymvfee c_anyaddmfee lg_anyaddmfee c_anyinctv lg_anyinctv; /*Available Unit Form Summary*/ proc summary data = avuflags2 noprint; class control; var &rflag2; output out = rentalflags2 (where = (_type_ = 1)) mean = ; run; /*****END AVAILABLE UNIT FLAGS SECTION*****/ /*****MERGE SITE VISIT ANS AVAILAVBLE UNIT FORMS*****/ data rentalflags_m; merge rentalflags1 (in=a) rentalflags2 (drop = _type_ _freq_); by control; if a; run; proc sort data = rentalflags_m; by control; run; /*****CREATE FINAL FLAGS*****/ data rentalflags_fin; set rentalflags_m; site = substr(control, 1, 2); /*Final Flags*/ if c_approved = 1 and lg_approved = 1 then approved_flag = 1; if c_metagent = 1 and lg_metagent = 1 then metagent_flag = 1; if c_anyunit = 1 and lg_anyunit = 1 then anyunit_flag = 1; if c_nounit = 1 and lg_nounit = 1 then nounit_flag = 1; if c_anyunit = 1 and lg_anyunit = 1 then anyunit_flag = 1; if c_listitem = 1 and lg_listitem = 1 then listitem_flag = 1; /*shown unit from rental form*/ if anyunit_flag = 1 then do; if c_shown_unit = 1 and lg_shown_unit=1 then shown_unit_flag = 1; end; /*shown unit from availunit form*/ if c_shown_unit_avu =1 and lg_shown_unit_avu =1 then shown_unit_avu_flag =1; if c_shown_actual = 1 and lg_shown_actual = 1 then shown_actual_flag = 1; if c_toldrent = 1 and lg_toldrent = 1 then toldrent_flag = 1; if c_sdany = 1 and lg_sdany = 1 then sdany_flag = 1; if c_sbany = 1 and lg_sbany = 1 then sbany_flag = 1; if c_anyappfee = 1 and lg_anyappfee then anyappfee_flag = 1; if c_anymvfee = 1 and lg_anymvfee then anymvfee_flag = 1; if c_anyaddmfee = 1 and lg_anyaddmfee then anyaddmfee_flag = 1; if c_anyinctv = 1 and lg_anyinctv then anyinctv_flag = 1; *create effective deposit flag*; if sdany_flag = 1 or sbany_flag = 1 then edep_flag = 1; if approved_flag = 1; keep control approved_flag metagent_flag anyunit_flag nounit_flag anyunit_flag listitem_flag shown_unit_flag shown_actual_flag sdany_flag toldrent_flag sbany_flag anyappfee_flag anymvfee_flag anyaddmfee_flag anyinctv_flag edep_flag shown_unit_avu_flag; run; proc sort data = rentalflags_fin; by control; run; /*****END FINAL CLEANING*****/ /*****SAVE DATA*****/ data lgttech.ipflags; set rentalflags_fin; run; /*****END PROGRAM*****/