/*****************************************************************************/

/*****************************************************************************/ *Model Data Prep_071112.sas

*Author: Abt Associates Inc. *Created: 7/11/12 Purpose: This is SAS code that reorganizes the 316(b) stated preference (SP) survey data for use in model estimation. It generates 9 observations for each respondent (3 choice questions X 3 options). It also adds model weights

to the data. /*****************************************************************************/ /*****************************************************************************/

/*****************************************************************************/ *Import the experimental design for each SP survey region. /*****************************************************************************/ *Northeast;

PROC IMPORT DBMS=excelcs REPLACE

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\Experimental_design_all_regions_041112.xlsx"

OUT= WORK.Northeast_final_design; SHEET="Northeast";

RUN; *Pacific;

PROC IMPORT DBMS=excelcs REPLACE

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\Experimental_design_all_regions_041112.xlsx"

OUT= WORK.Pacific_final_design; SHEET="Pacific";

RUN; *National;

PROC IMPORT DBMS=excelcs REPLACE

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\Experimental_design_all_regions_041112.xlsx"

OUT= WORK.National_final_design; SHEET="National";

RUN; *Southeast;

PROC IMPORT DBMS=excelcs REPLACE

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\Experimental_design_all_regions_041112.xlsx"

OUT= WORK.Southeast_final_design; SHEET="Southeast";

RUN; *Inland;

PROC IMPORT DBMS=excelcs REPLACE

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\Experimental_design_all_regions_041112.xlsx"

OUT= WORK.Inland_final_design; SHEET="Inland";

RUN;

/*****************************************************************************/ *Import and re-format the mail survey data /*****************************************************************************/; *import the mail survey data from excel;

PROC IMPORT DBMS=excelcs REPLACE

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\mail_survey_data_052312.xls"

OUT= WORK.mail_survey_data_052312; SHEET="mail_survey_data_052312";

RUN; *Create separate data sets for each region;

*1=Northeast

2=Southeast

3=Inland

4=Pacific 5=National; Data northeast_surveydata; set mail_survey_data_052312; if region~=1 then delete;

Page 1drop region; run;

Data southeast_surveydata; set mail_survey_data_052312; if region~=2 then delete; drop region; run; Data inland_surveydata; set mail_survey_data_052312; if region~=3 then delete; drop region; run; data pacific_surveydata; set mail_survey_data_052312; if region~=4 then delete; drop region; run; data national_surveydata; set mail_survey_data_052312; if region~=5 then delete; drop region; run;

*Use a macro to loop through all of the regions; *"r" is for region, and "n" is for region number; %macro format(r,n); Data &r._final_design; set &r._final_design;

if Question=1 then do Question=4;end; if Question=2 then do Question=5;end; if Question=3 then do Question=6;end;RUN; *Split survey data by question; Data &r._surveydata_Q4; length RESPONSE QUESTION 3;set &r._surveydata; QUESTION=4;RESPONSE=q4;drop q4 q5 q6; RUN; Data &r._surveydata_Q5; length RESPONSE QUESTION 3;set &r._surveydata; QUESTION=5;RESPONSE=q5;drop q4 q5 q6; RUN; Data &r._surveydata_Q6; length RESPONSE QUESTION 3;set &r._surveydata; QUESTION=6;RESPONSE=q6;drop q4 q5 q6; RUN; Data &r._surveydata_Qall; set &r._surveydata_Q4 &r._surveydata_Q5 &r._surveydata_Q6; drop region batch q24 q240 q241 q242 q243 nopolicy q7chk; RUN; *Merge with the experimental design; proc sql; create table &r._surveydata_Qall_exp as select a.*,b.* from &r._final_design as a left join &r._surveydata_Qall as b on a.version=b.version and a.question=b.question; quit; Data &r._surveydata_Qall_exp; set &r._surveydata_Qall_exp; %macro char(c); &c._x=input(&c.,8.); drop &c.;rename &c._x=&c.;%mend; %char(Q18_1);%char(Q18_2);*%char(Q22_1);*%char(Q22_3);*%char(Q22_4);

RUN; *Split survey data by question by option; *Status Quo is labeled as 0, Option A is labeled as 1, and Option B is labeled as
2; Data &r._surveydata_Qall_exp_0; set &r._surveydata_Qall_exp;OPTION=0; com_fish=com_fish_0; fish_pop=fish_pop_0; fish_save=fish_save_0; aquatic=aquatic_0; cost=cost_0;RUN; Data &r._surveydata_Qall_exp_1; set &r._surveydata_Qall_exp;OPTION=1; com_fish=com_fish_1; fish_pop=fish_pop_1; fish_save=fish_save_1; aquatic=aquatic_1; cost=cost_1;RUN; Data &r._surveydata_Qall_exp_2; set &r._surveydata_Qall_exp;OPTION=2; com_fish=com_fish_2; fish_pop=fish_pop_2; fish_save=fish_save_2; aquatic=aquatic_2; cost=cost_2;RUN; Data &r._model_data; Length ID Version Response Choice Question Option com_fish fish_pop fish_save aquatic cost 8; set &r._surveydata_Qall_exp_0 &r._surveydata_Qall_exp_1 &r._surveydata_Qall_exp_2; drop com_fish_0 fish_pop_0 fish_save_0 aquatic_0 cost_0 com_fish_1 fish_pop_1 fish_save_1 aquatic_1 cost_1 com_fish_2 fish_pop_2 fish_save_2 aquatic_2 cost_2; if (response-1)=option then CHOICE=1; if (response-1)~=option and response~=9 then CHOICE=0; if response=9 then CHOICE="";

RUN;

Data &r._model_data; length ID Version Response Choice Question Option com_fish fish_pop fish_save aquatic cost q1_1 q1_2 q1_3 q1_4 q1_5 q1_6 q2 q3_1 q3_2 q3_3 q3_4 q3_5 q7_1 q7_2 q7_3 q7_4 q7_5 q7_6 q8_1 q8_2 q8_3 q8_4 q8_5 q8_6 q8_7 q8_8 q9_1 q9_2 q9_3 q9_4 q9_5 q9_6 q9_7 q9_8 q9_9 q9_10 q9_11 q10_1 q10_2 q10_3 q10_4 q10_5 q10_6 q11_1 q11_2 q12 q13 q14 q15 q16 q17 q18_1 q18_2 q19 q20 q21 q22_1 q22_2 q22_3 q22_4 q22_5 q23 8.; set &r._model_data;
Page 2RUN;

proc sort data=&r._model_data out=&r._model_data; by ID Question;RUN; Data &r._model_data; set &r._model_data;

region=&n.;

RUN; %mend; %format(northeast,1); %format(southeast,2); %format(inland,3); %format(pacific,4); %format(national,5);

*Re-formated data without weights; Data all_reg_model_data_wo_wgts;

set northeast_model_data southeast_model_data inland_model_data pacific_model_data national_model_data;

RUN;

/*****************************************************************************/ *Generate subgroup weights and merge with survey data /*****************************************************************************/; *Import population proportions for samples using subgroup weighting (Northeast, Southeast, and Pacific regions); PROC IMPORT OUT= WORK.Northeast_p_prop

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\SP_wgt_targets_071112.xlsx"

DBMS=excelcs REPLACE; SHEET="NE_p_prop";

RUN; PROC IMPORT OUT= WORK.Southeast_p_prop

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\SP_wgt_targets_071112.xlsx"

DBMS=excelcs REPLACE; SHEET="SE_p_prop";

RUN; PROC IMPORT OUT= WORK.Pacific_p_prop

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\SP_wgt_targets_071112.xlsx"

DBMS=excelcs REPLACE; SHEET="PAC_p_prop";

RUN;

*Macro to generate subgroup weights based on gender and educational attainment (Northeast and Southeast regions); %macro gen_edu(reg); Data ®._model_data_1; set ®._model_data; *q13 is gender

-->1=male -->2=female; *q14 is educational attainment >4 means bachelors degree or higher; if q13=1 and q14 LE 4 then M1=1; else M1=0; if q13=2 and q14 LE 4 then F1=1; else F1=0; if q13=1 and q14 > 4 then M2=1; else M2=0; if q13=2 and q14 > 4 then F2=1; else F2=0; *Make any blank combinations null; if q13="." then M1="."; if q13="." then M2="."; if q13="." then f1="."; if q13="." then f2="."; if q14="." then M1="."; if q14="." then M2="."; if q14="." then f1="."; if q14="." then f2=".";

Run; Data ®._p_prop_1; set ®._p_prop; drop definition count; IF sample="M1" then M1_p_prop=p_prop; IF sample="M2" then M2_p_prop=p_prop; IF sample="F1" then F1_p_prop=p_prop; IF sample="F2" then F2_p_prop=p_prop; drop sample p_prop;

RUN; proc sql;
Page 3create table ®._p_prop_2 as select

sum(m1_p_prop) as m1_p_prop, sum(m2_p_prop) as m2_p_prop, sum(f1_p_prop) as f1_p_prop, sum(f2_p_prop) as f2_p_prop from ®._p_prop_1; quit; Data ®._model_data_2; set ®._model_data_1; IF _N_ = 1 then set ®._p_prop_2; p_prop=m1*m1_p_prop + m2*m2_p_prop + f1*f1_p_prop + f2*f2_p_prop; drop m1_p_prop m2_p_prop f1_p_prop f2_p_prop;

RUN; *Sum up the number of observations falling in each category and divide by the number of rows per survey; proc sql; create table ®._model_data_3 as select

a.*, sum(m1)/9 as m1_sum, sum(m2)/9 as m2_sum, sum(f1)/9 as f1_sum, sum(f2)/9 as f2_sum from ®._model_data_2 as a; quit; Data ®._model_data_w_prop; set ®._model_data_3; tot_w=m1_sum+m2_sum+f1_sum+f2_sum; *Calculate the sample proportions; m1_s_prop=(m1_sum/tot_w); m2_s_prop=(m2_sum/tot_w); f1_s_prop=(f1_sum/tot_w); f2_s_prop=(f2_sum/tot_w); if m1=1 then s_prop=m1_s_prop; if m2=1 then s_prop=m2_s_prop; if f1=1 then s_prop=f1_s_prop; if f2=1 then s_prop=f2_s_prop; if (m1+m2+f1+f2)~=1 then s_prop="."; if (m1+m2+f1+f2)~=1 then p_prop="."; drop m1_sum m2_sum f1_sum f2_sum tot_w m1_s_prop m2_s_prop f1_s_prop f2_s_prop; drop m1 f1 m2 f2; w_var=p_prop/s_prop;

RUN; %mend; %gen_edu(Northeast); %gen_edu(Southeast); *Macro to generate subgroup weights based on race (white vs other racial categories) for the Pacific region; Data Pacific_model_data_1; set pacific_model_data; if q22_1="." and q22_2="." and q22_3="." and q22_4="." and q22_5="." then r_blank=1; if r_blank~=1 then R1=1; if q22_1=1 | q22_2=2 | q22_3=3 | q22_4=4 then R2=1; if r2=1 then r1=0; if r1=1 then r2=0; drop r_blank;

RUN; Data Pacific_p_prop_1; set Pacific_p_prop; if sample="R1" then r1_p_prop=p_prop; if sample="R2" then r2_p_prop=p_prop;

RUN; proc sql; create table Pacific_p_prop_2 as select sum(r1_p_prop) as r1_p_prop, sum(r2_p_prop) as r2_p_prop from Pacific_p_prop_1; quit; Data Pacific_model_data_2; set Pacific_model_data_1;

Page 4IF _N_ = 1 then set Pacific_p_prop_2;

p_prop=r1*r1_p_prop + r2*r2_p_prop; drop r1_p_prop r2_p_prop;

RUN; proc sql; create table pacific_model_data_3 as select

a.*, sum(r1) as r1_sum, sum(r2) as r2_sum from pacific_model_data_2 as a; quit; Data pacific_model_data_4; set pacific_model_data_3; if R1=1 then s_prop=(r1_sum/(r1_sum+r2_sum)); if R2=1 then s_prop=(r2_sum/(r1_sum+r2_sum)); drop R1 R2 r1_sum r2_sum; w_var=p_prop/s_prop;

RUN; *Import Inland and National weights generated through the raking procedure; PROC IMPORT OUT= WORK.inland_wgt

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\inland_weight.xls"

DBMS=excelcs REPLACE; SHEET="inland_wgt";

RUN; PROC IMPORT OUT= WORK.national_wgt

DATAFILE= "S:\Projects\Tyche\WA 4-05 (316b)\316b Survey\national_weight.xls"

DBMS=excelcs REPLACE; SHEET="national_wgt";

RUN; *Merge Inland and National weights with regional survey data; %macro rm(reg); proc sql; create table ®._model_data_1 as select a.*,b.* from ®._model_data as a left join ®._wgt as b on a.ID=b.ID; quit; Data ®._model_data_2; set ®._model_data_1; w_var=®._wgt; drop ®._wgt;

RUN; %mend; %rm(inland); %rm(national); *Merge the formatted data w/ weights back into one dataset; Data all_regions_model_data;

set northeast_model_data_3 southeast_model_data_3 inland_model_data_2 pacific_model_data_4 national_model_data_2;

RUN;

Page 5