sum two columns from two tables

[Amin]

Hi All

is it possible in SAS that sum two variables from different table and put it as a new variable in  a new table based on another variable as a key. for example like this :

table A :

ID	A	B
1	33	21
2	44	22
3	55	33

Table B :

ID	A	B
1	48	98
2	55	12
3	67	34

and in table c , sum A values and B values for same ID

Table C :

ID	A	B
1	81	119
2	99	34
3	122	67

[TarunKumar]

use below attcahed code;

data tablea;

input ID:$1. num_A num_B;

datalines;

1 33 21

2 44 22

3 55 33

;

run;

data tableB;

input ID:$1. num_A num_B;

datalines;

1 48 98

2 55 12

3 67 34

;

run;

proc sql;

create table table_new as

select a.id,a.num_a+b.num_a As Num_a,

a.num_b+b.num_b As Num_b

from tablea a

left join tableB b on a.id=b.id;

quit;

[octrout]

Hi, Can this be done in data step ?

[mkeintz]

Yes.  Since each table is sorted by ID, you can read them both, interleaving observations by ID.  Then just have an array of totals to update with each observations, and with the last obs for each ID, output those totals, as per the DATA WANT step below:

data tablea;
  input ID:$1. num_A num_B;
datalines;
1 33 21
2 44 22
3 55 33
run;

data tableB;
  input ID:$1. num_A num_B;
datalines;
1 48 98
2 55 12
3 67 34
run;

data want ;
  set tablea tableb;
  by id;
  array sums {2} _temporary_;
  array nums {2} num_a num_b;

  /* Starting an ID group?  Zero out the sums */
  if first.id then do I=1 to dim(sums);
    sums{I}=0;
  end;

  /* Increment the sums and copy results back to NUM_A and NUM_B */
  do I=1 to dim(sums);
    sums{i}+nums{I};
    nums{I}=sums{I};
  end;

  if last.id;   /* Subsetting IF */
run;

[octrout]

How do I subtract instead of sums ? Thanks ! 

/* Increment the sums and copy results back to NUM_A and NUM_B */
  do I=1 to dim(sums);
    sums{i}+nums{I};
    nums{I}=sums{I};
  end;

[mkeintz]

What values are you subtacting from what other values?

Do you always have exactly two tables, and are those tables identical in their ID structure?  If so, assuming you want to substract table B from table A, you can still interleave the data sets using a SET with a BY statement, and then use the DIF function, as in:

data want;

  set tableb tablea;

  by id;

  array nums {2} num_a num_b;

  do I=1 to dim(sums);

    nums{I}=dif(nums{I});

  end;

  if last.id;

run;

The DIF function is  X-lag(X).  So when the record-in-hand is the second record for a by-group (i.e. the tablea record), it's subtracting the prior value (i.e. from tableB above) from the current (tableA).   Of course when the record-in-hand is the beginning of a by-group, the result is nonsense, but the subsetting "if last.id;" statement excludes that record.

If you had used "set tablea tableb" instead, you would have gotten B-A.