Hi, Attached are my data and syntax files. I've also pasted the syntax below. The issue is that I need the last 2 sheets on the exported .xls file to list all 13 regions, with 0 values as appropriate, rather than excluding the regions with 0 values. I can't seem to get the list/sparse combo to work - it outputs the total sample, not the subsetted samples. /*** import current month's data ***/
proc import out = have_master
datafile = 'W:\Have\have.xls'
dbms = excel replace;
getnames = yes;
run;
/*** assign region ***/
data have_1;
set have_master;
format region 2.;
informat region 2.;
if county in (2) then region = 1;
if county in (30) then region = 2;
if county in (3) then region = 3;
if county in (16) then region = 4;
if county in (15) then region = 5;
if county in (13) then region = 6;
if county in (1,11,21) then region = 7;
if county in (5,9,14,17,20) then region = 8;
if county in (19,22,23) then region = 9;
if county in (4,8,18) then region = 10;
if county in (7,12) then region = 11;
if county in (10) then region = 12;
if county in (6) then region = 13;
run;
/*** create output datasets ***/
/** subset variables **/
data have_2;
set have_1;
format lh 1.;
format lc 1.;
format ulh 1.;
format ulc 1.;
if type in ('registered') then lh = 1;
if type in ('licensed') then lc = 1;
if type in ('illegal') and illegal_type in ('h')
then ulh = 1;
if type in ('illegal') and illegal_type in ('c')
then ulc = 1;
run;
/** tables **/
proc freq data = have_2;
where type in ('registered');
tables lh * region / nopercent norow nocol
out = lh;
run;
proc freq data = have_2;
where type in ('licensed');
tables lc * region / nopercent norow nocol
out = lc;
run;
proc freq data = have_2;
where type in ('illegal') and illegal_type in ('h');
tables ulh * region / nopercent norow nocol
out = ulh;
run;
proc freq data = have_2;
where type in ('illegal') and illegal_type in ('c');
table ulc * region / nopercent norow nocol
out = ulc;
run;
/*** export output to .xls ***/
proc export data = lh
outfile = 'W:\Have\want.xls'
dbms = excel replace;
sheet = 'lh';
proc export data = lc
outfile = 'W:\Have\want.xls'
dbms = excel replace;
sheet = 'lc';
proc export data = ulh
outfile = 'W:\Have\want.xls'
dbms = excel replace;
sheet = 'ulh';
proc export data = ulc
outfile = 'W:\Have\want.xls'
dbms = excel replace;
sheet = 'ulc';
run; Thank you!!!!
... View more
