A Complex Do Loop and One Record per Household

libname pin_data "K:\pindat\";
run;

/* creating one record per HH using all DPID related data */;
data pin_data.hh_dpid_onerec (keep=hhid sid pid dem_wt dmt1-dmt18 dmd1-dmd18 dmq1-dmq18 dst1-dst18 dsd1-dsd18 dsq1-dsq18) ;
set pin_data.hh_dpid_mean_weighted_enh;
by hhid;
where dpid not in (.);

retain i dmt1-dmt18 dmd1-dmd18 dmq1-dmq18 dst1-dst18 dsd1-dsd18 dsq1-dsq18;

array dmt{*} dmt1-dmt18;
array dmd{*} dmd1-dmd18;
array dmq{*} dmq1-dmq18;
array dst{*} dst1-dst18;
array dsd{*} dsd1-dsd18;
array dsq{*} dsq1-dsq18;

if first.hhid
then do j=1 to 18;
dmt{j}=0;
dmd{j}=0;
dmq{j}=0;
dst{j}=0;
dsd{j}=0;
dsq{j}=0;
end;

if first.hhid then i=0;
i+1;

dmt{i}=mt;
dmd{i}=md;
dmq{i}=mq;
dst{i}=st;
dsd{i}=sd;
dsq{i}=sq;
if last.hhid then output;

run;
/* creating one record per HH using all CHID related data */;

data pin_data.hh_chid_onerec (keep=hhid cmt1-cmt9 cmd1-cmd9 cmq1-cmq9 cst1-cst9 csd1-csd9 csq1-csq9) ;
set pin_data.hh_chid_mean_weighted_enh;
by hhid;
where chid not in ( . );
retain i cmt1-cmt9 cmd1-cmd9 cmq1-cmq9 cst1-cst9 csd1-csd9 csq1-csq9;

array cmt{*} cmt1-cmt9;
array cmd{*} cmd1-cmd9;
array cmq{*} cmq1-cmq9;
array cst{*} cst1-cst9;
array csd{*} csd1-csd9;
array csq{*} csq1-csq9;
if first.hhid then do j=1 to 9;
cmt{j}=0;
cmd{j}=0;
cmq{j}=0;
cst{j}=0;
csd{j}=0;
csq{j}=0;
end;
if first.hhid then i=0;
i+1;
cmt{i}=mt;
cmd{i}=md;
cmq{i}=mq;
cst{i}=st;
csd{i}=sd;
csq{i}=sq;
if last.hhid then output;
run;

/* creating one record per HHID using UCID based codes - UCID=0 and UCID=. not inlcuded */;
data pin_data.hh_ucid_onerec (keep=hhid umt1-umt362 umd1-umd362 umq1-umq362 ust1-ust362 usd1-usd362 usq1-usq362) ;
set pin_data.hh_ucid_mean_weighted_enh;
by hhid;
where ucid not in (.);
retain i umt1-umt362 umd1-umd362 umq1-umq362 ust1-ust362 usd1-usd362 usq1-usq362;
array umt{*} umt1-umt362;
array umd{*} umd1-umd362;
array umq{*} umq1-umq362;
array ust{*} ust1-ust362;
array usd{*} usd1-usd362;
array usq{*} usq1-usq362;
if first.hhid then
do j=1 to 362;
umt{j}=0;
umd{j}=0;
umq{j}=0;
ust{j}=0;
usd{j}=0;
usq{j}=0;
end;
if first.hhid then i=0;
i+1;
umt{i}=mt;
umd{i}=md;
umq{i}=mq;
ust{i}=st;
usd{i}=sd;
usq{i}=sq;
if last.hhid then output; run;
/* creating one record per HHID merging all DPID, CHID, UCID variablesNote that the first variable in each refers to -1, that is unknown code */;
proc sort data=pin_data.hh_dpid_onerec;
by hhid;
run;
proc sort data=pin_data.hh_chid_onerec;
by hhid;
run;
proc sort data=pin_data.hh_ucid_onerec;
by hhid;
run;
data pin_data.hhid_panel_onerec;merge pin_data.hh_dpid_onerec (in=dpid) pin_data.hh_chid_onerec(in=chid) pin_data.hh_ucid_onerec(in=ucid);
by hhid;
if dpid;
run;