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

**

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

**

SP Respondent and NR Demo Stats_06.14.13;

*Author: Ryan Stapler, Abt Associates Inc.

*Created: 6/4/2013;

*Purpose: This a SAS program that calculates summary statistics for demographic

characteristics of respondents and non-respondents to the 316(b) stated

preference (SP) survey.

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

**

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

**;

*SPECIFY INPUT AND OUTPUT

FILES****************************************************;

%let d=s; *(C or H or S drive);

%let mainloc=&d.:\Projects\Tyche\WA 4-05 (316b)\316b Survey\Demographic

Analysis\;

%let output=Demo_stats_&sysdate..xls;

%macro export(datafile,outfile,sheet);

PROC EXPORT DATA= &datafile

OUTFILE= &outfile

DBMS=EXCEL replace;

SHEET=&sheet;

RUN;

%mend;

*import survey data;

PROC IMPORT DBMS=excelcs REPLACE

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

Survey\all_reg_model_data_w_wgts_071112.xls"

OUT= WORK.all_models_w_wghts;

SHEET="all_models_w_wghts";

RUN;

proc sort data=all_models_w_wghts out=all_survey nodupkey;

by id;

RUN;

Data all_survey;

set all_survey;

drop version response choice question option com_fish fish_pop fish_save aquatic

cost

p_prop s_prop w_var f78;

*create a dummy for income greater than 60k;

if q23>4 then income_g60k=1;

if q23<5 then income_g60k=0;

if q23="" then income_g60k="";

*create dummy for white;

if q22_1 ge 1 then q22_1=1;

if q22_2 ge 1 then q22_2=2;

if q22_3 ge 1 then q22_3=3;

if q22_4 ge 1 then q22_4=4;

if q22_5 ge 1 then q22_5=5;

race_sum=sum(of q22_1-q22_5);

if race_sum=5 and q22_5=5 then do race=1; end;

if race_sum~=5 or q22_5~=5 then do race=0; end;

if race_sum<1 then do race=""; end;

*create separate variable for age class, with 3 bins;

if q12 ge 60 then age_class3=3;

if q12 le 59 then age_class3=2;

if q12 le 39 then age_class3=1;

if q12 < 1 then age_class3="";

*add a dummy for household size>2;

if q15 ge 3 then hhsize3plus=1;

if q15=1 or q15=2 then hhsize3plus=0;

if q15<1 then hhsize3plus="";

*add dummy for college educated;

if q14 ge 5 then college=1;

Page 1
if q14 le 4 then college=0;

if q14 < 1 then college="";

*Recode region to support transpose;

reg=cat("R",region);

*create new variable names;

employment=q19;

hhsize=q15;

hisp=q21;

comconsump=q11_1;

recconsump=q11_2;

gender=q13;

RUN;

*calculate mean and standard deviation across all groups by survey region;

proc sql;

create table all_mean_std_dev as select

reg,

mean(q2) as q2_avg,

std(q2) as q2_std

from all_survey group by reg;

quit;

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

use macro to compile results

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

;

%macro aq(q,d);

proc sql;

create table &q._&d._a_mean as select

"R_all" as reg,

"mean" as stat,

&d. as variable,

avg(&q.) as value

from all_survey

where &d. ge 0

group by &d.;

create table &q._&d._r_mean as select

reg,

"mean" as stat,

&d. as variable,

avg(&q.) as value

from all_survey

where &d. ge 0

group by &d., reg;

quit;

*calculate standard deviations for all regions as a whole and separately for each

region;

proc sql;

create table &q._&d._a_std as select

"R_all" as reg,

"std" as stat,

&d. as variable,

std(&q.) as value

from all_survey

where &d. ge 0

group by &d.;

create table &q._&d._r_std as select

reg,

"std" as stat,

&d. as variable,

std(&q.) as value

from all_survey

where &d. ge 0

group by &d., reg;

quit;

Data &q._&d.;

set &q._&d._r_mean

&q._&d._a_mean

&q._&d._r_std

&q._&d._a_std;

Page 2
value=round(value,0.01);

RUN;

proc sort data=&q._&d. out=&q._&d.;

by variable stat;

RUN;

proc transpose data=&q._&d. out=&q._&d._out;

by variable stat;

id reg;

RUN;

Data &q._&d._out;

format field $20.;

set &q._&d._out;

field="&d.";

drop _NAME_;

RUN;

%mend;

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

END MACRO

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

;

%macro aq2(l);

%aq(&l.,income_g60k);

%aq(&l.,gender);

%aq(&l.,employment);

%aq(&l.,race);

%aq(&l.,age_class3);

%aq(&l.,hhsize);

%aq(&l.,hhsize3plus);

%aq(&l.,hisp);

%aq(&l.,comconsump);

%aq(&l.,recconsump);

%aq(&l.,college);

Data &l._out;

set

&l._income_g60k_out

&l._gender_out

&l._employment_out

&l._race_out

&l._age_class3_out

&l._hhsize3plus_out

&l._hisp_out

&l._comconsump_out

&l._recconsump_out

&l._college_out;

RUN;

%export(&l._out,"&mainloc.&output",&l._mn);*mn indicates main survey;

%mend;

%aq2(q2);

%aq(q15,gender);

%export(q15_gender_out,"&mainloc.&output",q15_gender_mn);*mn indicates main

survey;

***********************************************************************;

*IMPORT THE NONRESPONSE DATA;

PROC IMPORT DBMS=excelcs REPLACE

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

testing\nonresponse_data_060712.xls"

OUT= WORK.Nonresponse;

RUN;

*import the nonresponse data for the Northeast;

PROC IMPORT DBMS=excelcs REPLACE

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

testing\Northeast_NR_data_07.05.12rws.xls"

OUT= WORK.Nonresponse_NE;

RUN;

Page 3
Data nonresponse_ne;

set nonresponse_ne;

idcombx=input(idcomb,8.);

q16cmb_4x=input(q16cmb_4,1.);

q16cmb_8x=input(q16cmb_8,1.);

drop idcomb q16cmb_4 q16cmb_6 q16cmb_7 q16cmb_8;

rename idcombx=idcomb q16cmb_4x=q16cmb_4

q16cmb_8x=q16cmb_8;

RUN;

Data nonresponse;

set nonresponse nonresponse_ne;

RUN;

Data nonresponse_1;

set nonresponse;

keep mode idcomb regioncomb

q1comb q2comb q3comb q4comb q8comb q10comb q13comb q14comb q15comb

q16cmb_1 q16cmb_2 q16cmb_3 q16cmb_4 q16cmb_5 q16cmb_6 q16cmb_7 q16cmb_8 q18comb;

RUN;

*make sure remaining variables are numeric;

Data nonresponse_2;

set nonresponse_1;

q16cmb_1x=input(q16cmb_1,2.);

q16cmb_2x=input(q16cmb_2,2.);

q16cmb_3x=input(q16cmb_3,2.);

q16cmb_6x=input(q16cmb_6,2.);

q16cmb_7x=input(q16cmb_7,2.);

drop q16cmb_1 q16cmb_2 q16cmb_3 q16cmb_6 q16cmb_7;

rename

q16cmb_1x=q16cmb_1

q16cmb_2x=q16cmb_2

q16cmb_3x=q16cmb_3

q16cmb_6x=q16cmb_6

q16cmb_7x=q16cmb_7;

*adjust for blanks;

if q8comb=9 then q8comb="";

if q3comb=8 then q3comb="";

if q3comb=9 then q3comb="";

if q4comb=99 then q4comb="";

if q15comb=8 then q15comb="";

if q15comb=9 then q15comb="";

if q18comb=98 then q18comb="";

if q18comb=99 then q18comb="";

if q13comb=8 then q13comb="";

if q13comb=9 then q13comb="";

if q14comb=8 then q14comb="";

if q14comb=9 then q14comb="";

if q10comb=8 then q10comb="";

if q10comb=9 then q10comb="";

*3 observations have household listed as zero, treat as blanks;

if q4comb=0 then q4comb="";

RUN;

Data nonresponse_3;

set nonresponse_2;

*income;

if q18comb>4 then income_g60k=1;

if q18comb<5 then income_g60k=0;

if q18comb="" then income_g60k="";

*race;

*create dummy for white;

if q16cmb_1 ge 1 then q16cmb_1=1;

if q16cmb_2 ge 1 then q16cmb_2=2;

if q16cmb_3 ge 1 then q16cmb_3=3;

if q16cmb_4 ge 1 then q16cmb_4=4;

if q16cmb_5 ge 1 then q16cmb_5=5;

race_sum=sum(of q16cmb_1-q16cmb_5);

if race_sum=5 and q16cmb_5=5 then do race=1; end;

if race_sum~=5 or q16cmb_5~=5 then do race=0; end;

if race_sum<1 then do race=""; end;

*create separate variable for age class, with 3 bins;

Page 4
if q1comb ge 60 then age_class3=3;

if q1comb le 59 then age_class3=2;

if q1comb le 39 then age_class3=1;

if q1comb < 1 then age_class3="";

*add a dummy for household size>2;

if q4comb=0 then q4comb="";

if q4comb=99 then q4comb="";

if q4comb ge 3 then hhsize3plus=1;

if q4comb=1 or q4comb=2 then hhsize3plus=0;

if q4comb<1 then hhsize3plus="";

*add dummy for college educated;

if q3comb ge 5 then college=1;

if q3comb le 4 then college=0;

if q3comb < 1 then college="";

*Recode region to support transpose;

reg=cat("R",regioncomb);

*create a new variables;

employment=q8comb;

hhsize=q4comb;

hisp=q15comb;

comconsump=q13comb;

recconsump=q14comb;

gender=q2comb;

RUN;

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

USE MACRO TO CALCULATE MEANS AND STANDARD DEVIATIONS

***********************************************************;

%macro aq_nr(q,d);

proc sql;

create table &q._&d._a_nr_mean as select

"R_all" as reg,

"mean" as stat,

&d. as variable,

avg(&q.) as value

from nonresponse_3

where &d. ge 0

group by &d.;

create table &q._&d._r_nr_mean as select

reg,

"mean" as stat,

&d. as variable,

avg(&q.) as value

from nonresponse_3

where &d. ge 0

group by &d., reg;

quit;

*calculate standard deviations for all regions as a whole and separately for each

region;

proc sql;

create table &q._&d._a_nr_std as select

"R_all" as reg,

"std" as stat,

&d. as variable,

std(&q.) as value

from nonresponse_3

where &d. ge 0

group by &d.;

create table &q._&d._r_nr_std as select

reg,

"std" as stat,

&d. as variable,

std(&q.) as value

from nonresponse_3

where &d. ge 0

group by &d., reg;

quit;

Data &q._&d._nr;

set &q._&d._r_nr_mean

&q._&d._a_nr_mean

Page 5
&q._&d._r_nr_std

&q._&d._a_nr_std;

value=round(value,0.01);

RUN;

proc sort data=&q._&d._nr out=&q._&d._nr;

by variable stat;

RUN;

proc transpose data=&q._&d._nr out=&q._&d._nr_out;

by variable stat;

id reg;

RUN;

Data &q._&d._nr_out;

format field $20.;

set &q._&d._nr_out;

field="&d.";

drop _NAME_;

RUN;

%mend;

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

END MACRO

********************************************************************************;

%macro aq2(l);

%aq_nr(&l.,income_g60k);

%aq_nr(&l.,gender);

%aq_nr(&l.,employment);

%aq_nr(&l.,race);

%aq_nr(&l.,age_class3);

%aq_nr(&l.,hhsize);

%aq_nr(&l.,hhsize3plus);

%aq_nr(&l.,hisp);

%aq_nr(&l.,comconsump);

%aq_nr(&l.,recconsump);

%aq_nr(&l.,college);

Data &l._nr_out;

set

&l._income_g60k_nr_out

&l._gender_nr_out

&l._employment_nr_out

&l._race_nr_out

&l._age_class3_nr_out

&l._hhsize3plus_nr_out

&l._hisp_nr_out

&l._comconsump_nr_out

&l._recconsump_nr_out

&l._college_nr_out;

RUN;

%export(&l._nr_out,"&mainloc.&output",&l._nr);*nr for nonresponse survey;

%mend;

%aq2(q10comb);

Data nr_hisp_ne;

set nonresponse_3;

if reg~="R1" then delete;

if q15comb~=1 then delete;

RUN;

*summarize responses across groups;

proc sql;

create table nonresponse_all as select

mean(q10comb) as q10comb_mean,

std(q10comb) as q10comb_std

from nonresponse_3;

quit;

*summarize response by region;

proc sql;

create table nonresponse_R as select

reg,

mean(q10comb) as q10comb_mean format=10.2,

std(q10comb) as q10comb_std format=10.2

from nonresponse_3

Page 6
group by reg;

quit;

Page 7