topic Re: Calculate the average value from two data sets when occurs duplication in specific column in SAS Programming

Calculate the average value from two data sets when occurs duplication in specific column

Efka — Mon, 08 Apr 2013 20:29:12 GMT

Hello,

I have two data sets, where one column is exactly the same and another holds different values. There are two simple data sets:

First data set: Second data set:

OBS Name Income OBS Name Income

1 John 1201 1 John 5566

2 David 1602 2 David 5981

3 Karen 1553 3 Karen 8930

As You can see, the names are duplicating, however the income column is different.

The question would be - how can I make a single data set where:

1) The names do not duplicate anymore.

2) The data in 'income' column would be calculated as an average value from first and second data sets.

3) If possible, are there more than one solution for this situation? Please suggest the most efficient ways to do this,

because I'll need to apply suggested solution for many big data sets.

The output should be:

New data set

Name Income

John 3383,5 which is average of two previous data sets - ((1201+5566)/2)

David 3791,5

Karen 5241,5

Thank You.

Re: Calculate the average value from two data sets when occurs duplication in specific column

SandyH — Tue, 09 Apr 2013 00:47:38 GMT

1. set the two datasets with two columns (name, income).

2. proc sql;

avg(income) as income

group by name

merge the two datasets by name, with three columns. then calculate average.

Re: Calculate the average value from two data sets when occurs duplication in specific column

art297 — Tue, 09 Apr 2013 04:14:07 GMT

or. slightly faster:

proc sql;

create table want as

select name,avg(income) as income

from

(select * from one

outer union corr

select * from two)

group by name

;

quit;

Re: Calculate the average value from two data sets when occurs duplication in specific column

Sudhakar_A — Tue, 09 Apr 2013 04:50:40 GMT

another simple method.

data fin;

merge one two(rename=(income=income1));

by name;

avgincome=mean(income, income1);

run;