Calcite | Level 5

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Calcite | Level 5

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

another simple method.

data fin;

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

by name;

avgincome=mean(income, income1);

run;

3 REPLIES 3
Calcite | Level 5

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

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

2. proc sql;

avg(income) as income

group by name

OR

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

Opal | Level 21

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

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;

Calcite | Level 5

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

another simple method.

data fin;

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

by name;

avgincome=mean(income, income1);

run;

Discussion stats
• 3 replies
• 7799 views
• 6 likes
• 4 in conversation