Applying single observations from one dataset to many in another dataset

[jo_rod]

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.

[Reeza]

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

[jo_rod]

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

[novinosrin]

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

[jo_rod]

Yes, that was a mistake. It should be 2 1 3

[novinosrin · Accepted solution]

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;

[art297]

@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

[jo_rod]

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