SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Applying single observations from one dataset to many in another dataset

Accepted Solution Solved
Reply
Highlighted
Occasional Contributor
Posts: 11
Accepted Solution

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
a week ago
PROC Star
Posts: 359

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;

View solution in original post


All Replies
Super User
Posts: 20,236

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: 359

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

Posted in reply to novinosrin
Yes, that was a mistake. It should be 2 1 3
Solution
a week ago
PROC Star
Posts: 359

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: 7,539

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 193 views
  • 2 likes
  • 4 in conversation