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

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

*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 1
drop 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 2
RUN;

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 3
create 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 4
IF _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