BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jo_rod
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

7 REPLIES 7
Reeza
Super User

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

jo_rod
Obsidian | Level 7

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

novinosrin
Tourmaline | Level 20

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
Obsidian | Level 7
Yes, that was a mistake. It should be 2 1 3
novinosrin
Tourmaline | Level 20

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
Opal | Level 21

@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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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