Hi,
I have a table with two rows in it, and 18 variables. The first row contains summary values from other stuff and the second row is my summary of the same values (it's a quality control check). I want to make sure that my totals match the totals from the other source.
So, two rows, 18 columns. Looks vaguely like this:
c4 c5 c6 etc....
all 100 293 284
confirm 100 291 284
And i want to add a row that compares the all value in C4 with the comfirm value for C4, etc. So i end up with this:
c4 c5 c6 etc....
all 100 293 284
confirm 100 291 284
match 1 0 1
Thanks,
Megan
pw. Once i get this to work, it'll be applied to about 56 different tables, so it's not that i just don't want to eye scan the values themselves. There are going to be quite a few of them.
Thanks.
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;
I might suggest instead of mixing the two data sources (you don't say how you're doing this) that perhaps using PROC COMPARE may be better option.
I suppose that could work, but if someone knew how to add another row so that i could have both totals all together with a match flag all in the same table, that'd be ideal.
Actually, i just looked into and it's not really what i want. I don't want all the flags, i have to pull out everything but the total columns b/c they come back mismatched.
I'd really like a way to compare row one with row two and create a third row with a flag at the same time.
What about transposing data? It becomes trivial in the long form. Are all variables numeric?
That's what i'm fiddling with now. Yes, they are all numeric, and i just finished transposing both tables and am writing the compare code now. Just wasn't sure it would work so i hadn't updated the post. 🙂
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;
If you really wanted to keep it in wide format you could use arrays/retain/end.
Untested:
Data want;
Set have end=eof;
Array c(*) c1-c20;
Array c_lag(*) clag1-clag20;
Retain clag1-clag20;
If _n_=1 then do;
Do i=1 to dim(c);
C_lag(I) = c(I);
End;
Output;
If eof then do;
Do I=1 to 20;
C(I)=round(c(I)-c_lag(I), 0.1);
End;
Output;
End;
Run;
It would be very easy for IML . Do you want IML code ? The following is SQL code . data have; input c4 c5 c6 ; cards; 100 293 284 100 291 284 ; run; proc transpose data=have(obs=0) out=temp; var _all_; run; proc sql noprint; select catx(' ','range(',_name_,')=0 as ',_name_) into : list separated by ',' from temp; create table matched as select &list from have; quit; data want; set have matched; run;
I don't know what IML code is.
And i'm unfamiliar with the range function. I've looked it up now, but the SAS page doesn't have =0 on it. What's that part? I've run what you sent and i see that it's working, but i'm not clear on how selecting "range( c4 )=0 as c4,range( c5 )=0 as c5,range( c6 )=0 as c6" from have gives me the 1/0s that i'm looking at, lol. How did that work?? Can you please explain the range()=0 function a little more?
Thanks
Use PROC COMPARE.
proc compare out=want outall
data=have(obs=1)
compare=have(firstobs=2)
;
run;
You might want to create a macro to do it if you need to run it for 56 separate data set.
Thanks, but proc compare isn't what i want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.