topic Re: Preventing overwrite in SAS Procedures

Preventing overwrite

learn2 — Fri, 18 Jan 2019 19:27:02 GMT

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

Re: Preventing overwrite

novinosrin — Fri, 18 Jan 2019 19:33:59 GMT

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

Re: Preventing overwrite

learn2 — Fri, 18 Jan 2019 19:37:48 GMT

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

Re: Preventing overwrite

Astounding — Fri, 18 Jan 2019 19:39:31 GMT

What would you like the result to be?

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

Re: Preventing overwrite

novinosrin — Fri, 18 Jan 2019 19:41:38 GMT

data want;

set a b;

by id;

run

What you need is interleave and not a merge

Re: Preventing overwrite

novinosrin — Fri, 18 Jan 2019 19:44:27 GMT

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;

Re: Preventing overwrite

novinosrin — Fri, 18 Jan 2019 19:46:39 GMT

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;

Re: Preventing overwrite

learn2 — Fri, 18 Jan 2019 19:50:14 GMT

Thank you!