topic Re: How to append data and remove duplicates in SAS with some constraints in SAS Enterprise Guide

How to append data and remove duplicates in SAS with some constraints

eagles_dare13 — Wed, 21 May 2014 19:33:28 GMT

I have a data set A which has values like:
1
1
2
3
3
4
5
5

(a.k.a some values are repeating)

I have another data set B with values like:

1
3
3
5
7
7
8

(a.k.a some values are repeating , some values are also in A, and some are new)

I want to create a dataset C such that it has every value from A and only those from B that are not in A.
So the final should look like:

1
1
2
3
3
4
5
5
7
7
8

So in short, duplicates within the same set are to be retained but if there is a duplicate across A and B, only retain the one from A. There is also a description column that says whether the value is in A or B.

How to do this?

Re: How to append data and remove duplicates in SAS with some constraints

TomKari — Wed, 21 May 2014 19:55:22 GMT

What about a case where A has

and B has

Should your result have one 3 or two 3's?

Also, do you need any other variables copied across, or only this one?

Tom

Re: How to append data and remove duplicates in SAS with some constraints

Astounding — Wed, 21 May 2014 20:04:50 GMT

It may be short but it's not that simple.

data want;

from_a=0;

merge a (in=from_a) b (in=from_b);

by value;

if first.value then do;

if from_a and from_b then source='Both A and B';

else if from_a then source='A only';

else source='B only';

end;

retain source;

if source='B only' then output;

else if from_a then output;

run;

Re: How to append data and remove duplicates in SAS with some constraints

slchen — Wed, 21 May 2014 20:12:04 GMT

DATA A;
INPUT X;
CARDS;
1
1
2
3
3
4
5
5
;
RUN;

DATA B;
INPUT X;
CARDS;
1
3
3
5
7
7
8
;
RUN;

PROC SQL;
SELECT * FROM A
UNION ALL
SELECT * FROM B WHERE X NOT IN (SELECT * FROM A);
QUIT;

Re: How to append data and remove duplicates in SAS with some constraints

eagles_dare13 — Wed, 21 May 2014 20:19:56 GMT

In that case, it should have one 3 only.

Also there are other variables, but they are descriptive, no decision is made based on those variables.

Re: How to append data and remove duplicates in SAS with some constraints

Ksharp — Thu, 22 May 2014 13:41:23 GMT

proc sql;
create table want as
 select * from a
  union all
( select * from b
   except all
  select * from a)
;
run;

Xia Keshan

Re: How to append data and remove duplicates in SAS with some constraints

eagles_dare13 — Thu, 22 May 2014 17:53:22 GMT

When SAS concatenates data sets with the union all statement and each data set has the exact same column names and column data types, will SAS use the ordering of the columns to figure out the corresponding columns or the actual names?

Re: How to append data and remove duplicates in SAS with some constraints

Ksharp — Sat, 24 May 2014 10:35:35 GMT

union all corr