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

**

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

** 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 1if 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 2value=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 3Data 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 4if 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 6group by reg;

quit;

Page 7