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

**

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

**

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 1
option,

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 3
set &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 4
Southeast_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 5
format 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