topic Re: sum two columns from two tables in SAS Programming

sum two columns from two tables

Amin — Mon, 17 Feb 2014 12:08:59 GMT

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

Re: sum two columns from two tables

TarunKumar — Mon, 17 Feb 2014 12:18:37 GMT

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;

Re: sum two columns from two tables

octrout — Tue, 02 May 2017 20:23:17 GMT

Hi, Can this be done in data step ?

Re: sum two columns from two tables

mkeintz — Tue, 02 May 2017 22:49:00 GMT

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;

Re: sum two columns from two tables

octrout — Wed, 03 May 2017 03:27:54 GMT

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;

Re: sum two columns from two tables

mkeintz — Wed, 03 May 2017 14:25:41 GMT

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.