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

Hi I have two datasets that I would like to merge by id, but when I do that, I have Dataset B overwriting the diagnosis date, cluster and servdate in Dataset A, how do I prevent this?

 

Dataset A:

ID

Diagnosis Date

Cluster

Servdate

1

Jan 1 1998

Urban

Aug 3 1998

1

Jan 1 1998

Urban

Aug 15 1998

2

Feb 3 2000

Urban

April 9 2000

 

Dataset B:

ID

Diagnosis Date

Cluster

Servdate

1

Jan 1 1998

Rural

Sept 1 1998

1

Jan 1 1998

Rural

Sept 3 1998

2

Feb 3 2000

Rural

May 7 2000

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

worked out example using your sample

 


data a;
infile cards truncover;
input (ID	DiagnosisDate	Cluster	Servdate) (& $15.);
cards;
1	Jan 1 1998	Urban	Aug 3 1998
1	Jan 1 1998	Urban	Aug 15 1998
2	Feb 3 2000	Urban	April 9 2000
;

data b;
infile cards truncover;
input (ID	DiagnosisDate	Cluster	Servdate) (& $15.);
cards;
1	Jan 1 1998	Rural	Sept 1 1998
1	Jan 1 1998	Rural	Sept 3 1998
2	Feb 3 2000	Rural	May 7 2000
;

data want;
set a b;
by id;
run;

View solution in original post

7 REPLIES 7
novinosrin
Tourmaline | Level 20

Decide which ones you want to keep and drop the rest using dataset options

learn2
Fluorite | Level 6

This is what I need:

ID

Diagnosis Date

Cluster

Servdate

1

Jan 1 1998

Urban

Aug 3 1998

1

Jan 1 1998

Urban

Aug 15 1998

1

Jan 1 1998

Rural

Sept 1 1998

1

Jan 1 1998

Rural

Sept 3 1998

2

Feb 3 2000

Urban

April 9 2000

2

Feb 3 2000

Rural

May 7 2000

Astounding
PROC Star

What would you like the result to be?

 

Seeing what you posted as the result, change the MERGE statement to a SET statement.

learn2
Fluorite | Level 6

Thank you!

novinosrin
Tourmaline | Level 20

data want;

set a b;

by id;

run

 

What you need is interleave and not a merge

novinosrin
Tourmaline | Level 20

worked out example using your sample

 


data a;
infile cards truncover;
input (ID	DiagnosisDate	Cluster	Servdate) (& $15.);
cards;
1	Jan 1 1998	Urban	Aug 3 1998
1	Jan 1 1998	Urban	Aug 15 1998
2	Feb 3 2000	Urban	April 9 2000
;

data b;
infile cards truncover;
input (ID	DiagnosisDate	Cluster	Servdate) (& $15.);
cards;
1	Jan 1 1998	Rural	Sept 1 1998
1	Jan 1 1998	Rural	Sept 3 1998
2	Feb 3 2000	Rural	May 7 2000
;

data want;
set a b;
by id;
run;
novinosrin
Tourmaline | Level 20

Or if your datasets are not sorted by id , you could use proc sql

 

proc sql;
create table want as
select * from a
union all
select * from b
order by id, DiagnosisDate,Servdate;
quit;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2042 views
  • 0 likes
  • 3 in conversation