*********************************************************************************

**

*********************************************************************************

** Split-Sample Ext Val Test Code_Opt and Ques_01.16.13.sas *Author: Ryan Stapler, Abt Associates Inc. Created: 11/2/12 Updated: 1/17/13; *Purpose: This a SAS program that runs split-sample external validity tests on the 316(b) stated preference survey data with data split by option and choice question.

*********************************************************************************

**

********************************************************************************* **; *specify input and output files; %let d=s; *(C or H drive); %let mainloc=&d.:\Projects\Tyche\WA 4-05 (316b)\316b Survey\; %let scopeloc=&mainloc.scope tests\; %let survey_data=all_reg_model_data_w_wgts_071112.xls; %let outfile=extscope_within_opts_qsep_&sysdate.;

*define import and export macros; %macro import(infile,insheet,out);
PROC IMPORT OUT= WORK.&out

DATAFILE= "&infile"
DBMS=EXCEL REPLACE; SHEET="&insheet.$";
GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; %mend; %macro export(datafile,outfile,sheet); PROC EXPORT DATA= &datafile

OUTFILE= &outfile DBMS=EXCEL replace; SHEET=&sheet;
RUN; %mend;

%import(&mainloc.&survey_data.,all_models_w_wghts,survey_data);

*Fish Saved levels for each region Northeast 95%, 50%, 5% Southeast 90%, 55%, 25% Pacific 95%, 50%, 2% Inland 95%, 75%, 55% National 95%, 55%, 25%;

*Region #'s 1=Northeast 2=Southeast 3=Inland 4=Pacific 5=National;

*********************************************************** *the following macro compiles the responses to choice questions (number of yes's and no's) for each region and question, these individual data sets are required to run the chi-sq tests of proportions; %macro chisq_opt_q(r,p,n,q); *r is region (long name, not number), p is percent fish saved, n is region number, and q is question number (4, 5, or 6); Proc sql; create table &r._&p._q&q. as select region, Page 1option,

mean(fish_save) as fish_save, sum(choice) as yes, count(choice) as sample, mean(choice) as choice_mean, std(choice) as choice_std, mean(com_fish) as com_fish_mean, std(com_fish) as com_fish_std, mean(fish_pop) as fish_pop_mean, std(fish_pop) as fish_pop_std, mean(aquatic) as aquatic_mean, std(aquatic) as aquatic_std, mean(cost) as cost_mean, std(cost) as cost_std from survey_data where region=&n. and fish_save=&p. and response >=0 and question=&q. group by option, region; quit; Data &r._&p._q&q.; format region best12.; format option best12.; format question best12.; set &r._&p._q&q.; question=&q.;
RUN; Data &r._&p._q&q.x1; set &r._&p._q&q.; no=sample-yes; keep option yes no;
RUN; proc transpose data=&r._&p._q&q.x1 out=&r._&p._q&q.x1; by option;
RUN; Data &r._&p._q&q.x1; set &r._&p._q&q.x1; rename _NAME_=choice col1=count; label choice=choice count=count;
RUN; %mend; %chisq_opt_q(Northeast,95,1,4); %chisq_opt_q(Northeast,95,1,5); %chisq_opt_q(Northeast,95,1,6); %chisq_opt_q(Northeast,50,1,4); %chisq_opt_q(Northeast,50,1,5); %chisq_opt_q(Northeast,50,1,6); %chisq_opt_q(Northeast,5,1,4); %chisq_opt_q(Northeast,5,1,5); %chisq_opt_q(Northeast,5,1,6);

%chisq_opt_q(Southeast,90,2,4); %chisq_opt_q(Southeast,90,2,5); %chisq_opt_q(Southeast,90,2,6); %chisq_opt_q(Southeast,55,2,4); %chisq_opt_q(Southeast,55,2,5); %chisq_opt_q(Southeast,55,2,6); %chisq_opt_q(Southeast,25,2,4); %chisq_opt_q(Southeast,25,2,5); %chisq_opt_q(Southeast,25,2,6);

%chisq_opt_q(Inland,95,3,4); %chisq_opt_q(Inland,95,3,5); %chisq_opt_q(Inland,95,3,6); %chisq_opt_q(Inland,75,3,4); %chisq_opt_q(Inland,75,3,5); %chisq_opt_q(Inland,75,3,6); %chisq_opt_q(Inland,55,3,4); %chisq_opt_q(Inland,55,3,5); %chisq_opt_q(Inland,55,3,6);

%chisq_opt_q(Pacific,95,4,4); %chisq_opt_q(Pacific,95,4,5); %chisq_opt_q(Pacific,95,4,6); %chisq_opt_q(Pacific,50,4,4); %chisq_opt_q(Pacific,50,4,5); %chisq_opt_q(Pacific,50,4,6); %chisq_opt_q(Pacific,2,4,4); %chisq_opt_q(Pacific,2,4,5); %chisq_opt_q(Pacific,2,4,6);

%chisq_opt_q(National,95,5,4); %chisq_opt_q(National,95,5,5); %chisq_opt_q(National,95,5,6); Page 2%chisq_opt_q(National,55,5,4); %chisq_opt_q(National,55,5,5);

%chisq_opt_q(National,55,5,6); %chisq_opt_q(National,25,5,4); %chisq_opt_q(National,25,5,5); %chisq_opt_q(National,25,5,6);

*Northeast 95%, 50%, 5% Southeast 90%, 55%, 25% Pacific 95%, 50%, 2% Inland 95%, 75%, 55% National 95%, 55%, 25%;

*Region #'s 1=Northeast 2=Southeast 3=Inland 4=Pacific 5=National;

*********************************************************** Conduct chis-sq test of proportions within each question and option combination; ***********************************************************; %macro chisq_within_opts_q(r,p1,p2,p3,q); *r is region (long name, not number), p1 to p3 are levels of fish saved (high to low, varies by region), and q is question number (4, 5, or 6); Data &r._&p1._q&q.x1x; set &r._&p1._q&q.x1; fish_save=&p1.; question=&q.;
RUN;

Data &r._&p2._q&q.x1x; set &r._&p2._q&q.x1; fish_save=&p2.; question=&q.;
RUN;

Data &r._&p3._q&q.x1x; set &r._&p3._q&q.x1; fish_save=&p3.; question=&q.;
RUN;

Data &r._allper_q&q.; set &r._&p1._q&q.x1x &r._&p2._q&q.x1x &r._&p3._q&q.x1x;
RUN; Data &r._allper_Opt1_q&q.; set &r._allper_q&q.; if option~=1 then delete; drop option;
RUN; proc freq data=&r._allper_Opt1_q&q. order=data; weight count; table fish_save*choice/ chisq; output out=&r._allper_Opt1x_q&q. pchi;
RUN; Data &r._allper_Opt2_q&q.; set &r._allper_q&q.; if option~=2 then delete; drop option;
RUN; proc freq data=&r._allper_Opt2_q&q. order=data; weight count; table fish_save*choice/ chisq; output out=&r._allper_Opt2x_q&q. pchi;
RUN; Data &r._allper_Opt1x_q&q.; format region $20.; format option 1.; format question best12.; Page 3set &r._allper_Opt1x_q&q.;

option=1; region="&r."; question=&q.; format significance $3.; if P_PCHI le 0.10 then do significance="*"; end; if P_PCHI le 0.05 then do significance="**"; end; if P_PCHI le 0.01 then do significance="***"; end;
RUN; Data &r._allper_Opt2x_q&q.; format region $20.; format option 1.; format question best12.; set &r._allper_Opt2x_q&q.; option=2; region="&r."; question=&q.; format significance $3.; if P_PCHI le 0.10 then do significance="*"; end; if P_PCHI le 0.05 then do significance="**"; end; if P_PCHI le 0.01 then do significance="***"; end;
RUN; %mend; %chisq_within_opts_q(Northeast,95,50,5,4); %chisq_within_opts_q(Northeast,95,50,5,5); %chisq_within_opts_q(Northeast,95,50,5,6); %chisq_within_opts_q(Southeast,90,55,25,4); %chisq_within_opts_q(Southeast,90,55,25,5); %chisq_within_opts_q(Southeast,90,55,25,6); %chisq_within_opts_q(Pacific,95,50,2,4); %chisq_within_opts_q(Pacific,95,50,2,5); %chisq_within_opts_q(Pacific,95,50,2,6); %chisq_within_opts_q(Inland,95,75,55,4); %chisq_within_opts_q(Inland,95,75,55,5); %chisq_within_opts_q(Inland,95,75,55,6); %chisq_within_opts_q(National,95,55,25,4); %chisq_within_opts_q(National,95,55,25,5); %chisq_within_opts_q(National,95,55,25,6);

*********************************************************** SUMMARIZE RESULTS - the rest of this code summarizes results of the chi-sq tests; ***********************************************************;

*compile a dataset summarizing results of chi-square tests for each question and option; Data chisq_results_wn_opts_qsep;

Set Northeast_allper_Opt1x_q4 Northeast_allper_Opt1x_q5 Northeast_allper_Opt1x_q6 Northeast_allper_Opt2x_q4 Northeast_allper_Opt2x_q5 Northeast_allper_Opt2x_q6

Southeast_allper_Opt1x_q4 Southeast_allper_Opt1x_q5 Southeast_allper_Opt1x_q6 Southeast_allper_Opt2x_q4 Southeast_allper_Opt2x_q5 Southeast_allper_Opt2x_q6

Pacific_allper_Opt1x_q4 Pacific_allper_Opt1x_q5 Pacific_allper_Opt1x_q6 Pacific_allper_Opt2x_q4 Pacific_allper_Opt2x_q5 Pacific_allper_Opt2x_q6

Inland_allper_Opt1x_q4 Inland_allper_Opt1x_q5 Inland_allper_Opt1x_q6 Inland_allper_Opt2x_q4 Inland_allper_Opt2x_q5 Inland_allper_Opt2x_q6

National_allper_Opt1x_q4 National_allper_Opt1x_q5 National_allper_Opt1x_q6 National_allper_Opt2x_q4 National_allper_Opt2x_q5 National_allper_Opt2x_q6;
RUN;

*compile a dataset summarizing mean and st deviation for each variable; Data mean_and_stdev; format reg $10.;

set Northeast_5_q4 Northeast_5_q5 Northeast_5_q6 Northeast_50_q4 Northeast_50_q5 Northeast_50_q6 Northeast_95_q4 Northeast_95_q5 Northeast_95_q6 Page 4Southeast_25_q4 Southeast_25_q5 Southeast_25_q6

Southeast_55_q4 Southeast_55_q5 Southeast_55_q6 Southeast_90_q4 Southeast_90_q5 Southeast_90_q6 Pacific_2_q4 Pacific_2_q5 Pacific_2_q6 Pacific_50_q4 Pacific_50_q5 Pacific_50_q6 Pacific_95_q4 Pacific_95_q5 Pacific_95_q6 Inland_55_q4 Inland_55_q5 Inland_55_q6 Inland_75_q4 Inland_75_q5 Inland_75_q6 Inland_95_q4 Inland_95_q5 Inland_95_q6 National_25_q4 National_25_q5 National_25_q6 National_55_q4 National_55_q5 National_55_q6 National_95_q4 National_95_q5 National_95_q6; *Region #'s 1=Northeast 2=Southeast 3=Inland 4=Pacific 5=National; if region=1 then do reg="Northeast"; end; if region=2 then do reg="Southeast"; end; if region=3 then do reg="Inland"; end; if region=4 then do reg="Pacific"; end; if region=5 then do reg="National"; end;
RUN;

*export results for tests based on all questions;; %export(chisq_results_wn_opts_qsep,&outfile.,chisq_wn_opts_qsep); %export(mean_and_stdev,&outfile.,mean_and_stdev);

*combine the within option test results within the mean and standard deviations; proc sql; create table stats_wn_opts_qsep as select a.*, b.* from mean_and_stdev as a left join chisq_results_wn_opts_qsep as b on a.reg=b.region and a.option=b.option and a.question=b.question; quit; proc sort data=stats_wn_opts_qsep out=stats_wn_opts_qsep; by region option question;
RUN; %export(stats_wn_opts_qsep,&outfile.,stats_wn_opts_qsep);

*generate summary statistics for each region in a format consistent with tables in the peer review report; %macro s(rnum,qnum,onum,olet); *[region # (1/2/3/4/5), question # (4/5/6), option # (1/2), and option letter (A/B)]; Data stats_R&rnum._opt&onum._q&qnum.; set stats_wn_opts_qsep; if region~=&rnum. then delete; if option~=&onum. then delete; if question~=&qnum. then delete; drop yes sample reg region;
RUN; proc sort data=stats_R&rnum._opt&onum._q&qnum. out=stats_R&rnum._opt&onum._q&qnum.; by fish_save;
RUN; proc transpose data=stats_R&rnum._opt&onum._q&qnum. out=stats_R&rnum._opt&onum._q&qnum.; by fish_save;
RUN; proc sort data=stats_R&rnum._opt&onum._q&qnum. out=stats_R&rnum._opt&onum._q&qnum.; key fish_save / descending;
RUN; Data stats_R&rnum._opt&onum._q&qnum._x; format fish_save 2.; format _NAME_ $25.; Page 5format value $10.;

set stats_R&rnum._opt&onum._q&qnum.; if _n_=13 or _n_=14 or _n_=15 or _n_=28 or _n_=29 or _n_=30 then delete; *{13 14 15 28 29 30}; if _LABEL_~="" then _NAME_=_LABEL_; if _NAME_="Option" or _NAME_="question" then delete; r1=round(col1,.0001); drop _LABEL_; if _NAME_="Chi-Square" then r1=round(col1,.01); if _NAME_="P-value for Chi-Square" then r1=round(col1,0.001); *return the length of the field string; lfield=length(_NAME_); std=substr(_NAME_,lfield-2,3); if std="std" then value=cat("(",r1,")"); if std~="std" then value=r1; label _NAME_=field; rename _NAME_=field; drop col1 lfield std r1; rename value=ques&qnum._opt&olet.; order=_n_;
RUN; proc sort data=stats_R&rnum._opt&onum._q&qnum._x out=stats_R&rnum._opt&onum._q&qnum._x; by order;

run; %mend; *Northeast; %s(1,4,1,A); %s(1,4,2,B); %s(1,5,1,A); %s(1,5,2,B); %s(1,6,1,A); %s(1,6,2,B); *Southeast; %s(2,4,1,A); %s(2,4,2,B); %s(2,5,1,A); %s(2,5,2,B); %s(2,6,1,A); %s(2,6,2,B); *Inland; %s(3,4,1,A); %s(3,4,2,B); %s(3,5,1,A); %s(3,5,2,B); %s(3,6,1,A); %s(3,6,2,B); *Pacific; %s(4,4,1,A); %s(4,4,2,B); %s(4,5,1,A); %s(4,5,2,B); %s(4,6,1,A); %s(4,6,2,B); *National; %s(5,4,1,A); %s(5,4,2,B); %s(5,5,1,A); %s(5,5,2,B); %s(5,6,1,A); %s(5,6,2,B);

*compile stats for each region; %macro o(rnum); *region number; Data R&rnum._summary;

merge stats_R&rnum._opt1_q4_x stats_R&rnum._opt2_q4_x stats_R&rnum._opt1_q5_x stats_R&rnum._opt2_q5_x stats_R&rnum._opt1_q6_x stats_R&rnum._opt2_q6_x; by order; drop order;
RUN; %mend; %o(1);%o(2);%o(3);%o(4);%o(5);

*export summary results for each region; %export(R1_summary,&outfile.,R1_summary) %export(R2_summary,&outfile.,R2_summary) %export(R3_summary,&outfile.,R3_summary) %export(R4_summary,&outfile.,R4_summary) %export(R5_summary,&outfile.,R5_summary)

Page 6