## Applying single observations from one dataset to many in another dataset

Solved
Occasional Contributor
Posts: 11

# Applying single observations from one dataset to many in another dataset

[ Edited ]

Hi,

So I have these two datasets (example below) where I want to take the information from dataset B and apply it to dataset A. In other words, where the information for sex=1 in dataset B is applied to all the observations in dataset A where sex=1.

Dataset A

ID Sex  Race Age

1     1       3    20

1     2       2    39

2     2       1    27

1     1       1    32

2     2       2    22

Dataset A

Sex X  X1  X2

1     1   2    3

2     2   1    3

What I'm trying to get:

Dataset New

ID Sex  Race Age X  X1  X2

1     1       3    20   1   2    3

1     2       2    39   2   1    3

2     2       1    27   2   1    3

1     1       1    32   1   2    3

2     2       2    22   2   1    3

Does anyone know of a way in how I can do this? I've tried using:

data new;

if _n_=1 or _n_=2 then set b;

set a;

run;

This didn't work, it just made the responses for both sexes the same.

I would greatly appreciate any help.

Accepted Solutions
Solution
‎12-07-2017 05:14 PM
PROC Star
Posts: 1,569

## Re: Applying single observations from one dataset to many in another dataset

data a;
input ID Sex Race Age;
datalines;
1 1 3 20
1 2 2 39
2 2 1 27
1 1 1 32
2 2 2 22
;

data b;
input Sex X X1 X2;
datalines;
1 1 2 3
2 2 1 3
;

proc sql;
create table want as
select *
from a , b
where a.sex=b.sex;
quit;

All Replies
Super User
Posts: 23,260

## Re: Applying single observations from one dataset to many in another dataset

Try if _n_=1 but also make sure to include a BY statement - by Sex? or whatever variable you're merging together.

Occasional Contributor
Posts: 11

## Re: Applying single observations from one dataset to many in another dataset

I tried that too, but it still merges gives me the same problem of one line of observation being the same for everyone.

PROC Star
Posts: 1,569

## Re: Applying single observations from one dataset to many in another dataset

Dataset New

ID Sex  Race Age X  X1  X2

1     1       3    20   1   2    3

1     2       2    39   1   2    3/* is this correct or shoud it be 2   1    3?*/

2     2       1    27   2   1    3

1     1       1    32   1   2    3

2     2       2    22   2   1    3

Occasional Contributor
Posts: 11

## Re: Applying single observations from one dataset to many in another dataset

Yes, that was a mistake. It should be 2 1 3
Solution
‎12-07-2017 05:14 PM
PROC Star
Posts: 1,569

## Re: Applying single observations from one dataset to many in another dataset

data a;
input ID Sex Race Age;
datalines;
1 1 3 20
1 2 2 39
2 2 1 27
1 1 1 32
2 2 2 22
;

data b;
input Sex X X1 X2;
datalines;
1 1 2 3
2 2 1 3
;

proc sql;
create table want as
select *
from a , b
where a.sex=b.sex;
quit;

PROC Star
Posts: 8,146

## Re: Applying single observations from one dataset to many in another dataset

@novinosrin's code will work as long as you don't have any records that have a missing value for sex. If you do, and want/need to keep those records, I'd suggest:

```proc sql;
create table new as
select *
from A
left join B
on A.Sex eq B.Sex
;
quit;
```

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 11

## Re: Applying single observations from one dataset to many in another dataset

[ Edited ]

That's a good point. Thankfully I have complete data on sex. Thanks for all the help!

☑ This topic is solved.