topic Re: Applying single observations from one dataset to many in another dataset in SAS Data Management

Applying single observations from one dataset to many in another dataset

jo_rod — Thu, 07 Dec 2017 22:05:03 GMT

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.

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

Reeza — Thu, 07 Dec 2017 21:58:31 GMT

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

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

jo_rod — Thu, 07 Dec 2017 22:03:08 GMT

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

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

novinosrin — Thu, 07 Dec 2017 22:03:18 GMT

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

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

jo_rod — Thu, 07 Dec 2017 22:04:30 GMT

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

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

novinosrin — Thu, 07 Dec 2017 22:06:43 GMT

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;

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

art297 — Thu, 07 Dec 2017 22:13:37 GMT

@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

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

jo_rod — Thu, 07 Dec 2017 22:18:15 GMT

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