This works, but i'd like to leave the post open in case anyone knows of a function or something that can do it without the transposing. /*select out the original all row and rename the variables*/ data d1_part1_1a_selectrows; set final.d1_final (drop=_break_ i); where source=1 and cat_order in (1 7) and grp_order in (1 2); /*rename*/ %macro rename; %do i=4 %to 22; rename _c&i._=c&i.; %end; %mend; %rename; drop source; run; /*Transpose selectrows from wide to long*/ data d1_part1_2_transpose_all (drop=c: grp_order i); set d1_part1_1a_selectrows; array xvar(19) c4-c22; do i=1 to 19; where grp_char="All"; var=vname(xvar(i)); total=xvar(i); output; end; run; /*Get totals on your own to compare to the ALL row.*/ proc sql; create table d1_part1_3_confirm as select cat_order, %macro sum; %do i=4 %to 22; sum(C&i.) as C&i., %end; %mend; %sum "confirm" as grp_char from d1_part1_1a_selectrows where cat_order=1 group by cat_order; alter table d1_part1_3_confirm drop cat_order; quit; /*Transpose confirm from wide to long*/ data d1_part1_4_transpose_confirm (drop=c: i); set d1_part1_3_confirm; array xvar(19) c4-c22; do i=1 to 19; var=vname(xvar(i)); total=xvar(i); output; end; run; /*Merge old with new sums*/ proc sql; create table D1_part1_5_compare as select coalescec(a.var, b.var) as var, a.total as orig_total, b.total as confirm_total, case when a.total=b.total then 1 else 0 end as matchflag from D1_part1_2_transpose_all a full join D1_part1_4_transpose_confirm b on upcase(a.var)=upcase(b.var); quit;
... View more
