topic Subtracting values from 2 datasets/arrays in SAS Procedures

Subtracting values from 2 datasets/arrays

CharlesR — Mon, 30 Aug 2010 20:20:53 GMT

Hello,
I'm not sure if this is the correct forum to pose this question, but . . .
I have 2 datasets which have the same variables/observations. For my project., i need to subtract one from the other, but i have a small problem. If i go to do it with proc iml, creating arrays with the values, iml does not work if a value=0, and i happen to have quite a few values of zero. Anyone have ideas of what i can do? Thanks!

Re: Subtracting values from 2 datasets/arrays

Cynthia_sas — Mon, 30 Aug 2010 21:09:39 GMT

Hi:
First I had to make some data, so I used SASHELP.CLASS and made 2 different versions of it with some changed values (so there would be differences) and with some 0 values.

Then I used PROC SQL to join the two tables by NAME and do the subtraction. I kept EVERYTHING...so you could see what might be useful to you. If you did not want negative numbers, then you might use the ABS function to remove the - sign from the subtraction (as shown for the ABSAGEDIFF variable).

cynthia
[pre]
** make a version of SASHELP.CLASS with different values for;
** age, height and weight;
data class;
set sashelp.class;
age = age + 5;
height = height * 1.05;
weight = weight * 1.05;
** make a few 0 obs;
if name = 'John' then do;
age = 0;
height=0;
weight = 0;
end;
run;

** make some obs with 0 in a second copy of sashelp.class;
data class2;
set sashelp.class;
if name in ('Janet', 'Alfred') then do;
age = 0;
height=0;
weight = 0;
end;
run;

proc sql;
create table newdiff as
select a.name, a.sex, a.age, a.height, a.weight,
b.age as b_age, b.height as b_height, b.weight as b_weight,
a.age - b.age as agediff,
a.height - b.height as htdiff,
a.weight - b.weight as wtdiff,
abs(a.age - b.age) as absagediff
from work.class as a,
work.class2 as b
where a.name = b.name
order by name;
quit;

proc print data=work.newdiff;
run;
[/pre]

Re: Subtracting values from 2 datasets/arrays

Reeza — Mon, 30 Aug 2010 21:42:23 GMT

You may also want to consider PROC COMPARE depending on what you're looking for, particularily if its validation.

Even If its just two data sets with same variables/observations in the same order then Proc Compare can help automate some of that, see

http://www.lexjansen.com/pharmasug/2003/tutorials/tu056.pdf

Re: Subtracting values from 2 datasets/arrays

art297 — Mon, 30 Aug 2010 22:14:15 GMT

Charles,

If AND ONLY IF your data is exactly like you described, and there REALLY is a one for one match between the two files (i.e., if record N in the first file is always the same individual as record N in the second file) then a simple data step would also work.

For example, using Cynthia's test data (with one exception):

** make a version of SASHELP.CLASS with different values for;
** age, height and weight;
data class;
set sashelp.class;
age = age + 5;
height = height * 1.05;
weight = weight * 1.05;
** make a few 0 AND MISSINGobs;
if name = 'John' then do;
age = 0;
call missing(height);
weight = 0;
end;
run;

** make some obs with 0 in a second copy of sashelp.class;
data class2;
set sashelp.class;
if name in ('Janet', 'Alfred') then do;
age = 0;
height=0;
weight = 0;
end;
run;

data want;
set class;
set class2(keep=age height weight
rename=(age=b_age
height=b_height
weight=b_weight));
age_diff=sum(age-b_age);
height_diff=sum(height-b_height);
weight_diff=(weight-b_weight);
run;

HTH,
Art

Re: Subtracting values from 2 datasets/arrays

CharlesR — Thu, 02 Sep 2010 02:54:19 GMT

hey thanks both of you! I think i'm going with the data step! Appreciate the help as always!