turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Subtracting two SAS data sets

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-02-2015 04:51 PM - edited 12-02-2015 04:52 PM

I have two SAS data sets - one of them has about 600,000 more observations than the other. All of the observations from SAS data set A are also in SAS data Set B, B just has more observations in addition. How can I abstract only those ~600,000 observations that are different? I already tried PROC COMPARE with no luck, since the new data aren't in any particualr order. for example

Set A

X Y

Apples 1

Oranges 3

Pears 3

Set B

X Y

Apples 1

Watermellon 1

Oranges 3

Pears 3

Banana 2

in this case I want my output to be

X Y

Watermellon 1

Banana 2

Thoughts?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-02-2015 05:19 PM

If you don't insist on preserving the order of observations in the output dataset, it can be as simple as this:

```
data A;
length X $16;
input X $ Y;
cards;
Apples 1
Oranges 3
Pears 3
;
data B;
length X $16;
input X $ Y;
cards;
Apples 1
Watermellon 1
Oranges 3
Pears 3
Banana 2
;
proc sql;
create table diff as
select * from b
except all
select * from a;
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-02-2015 05:21 PM

proc sql;

create table unique as

select * from largedataset

except

select * from smallerdataset;

quit;

This will require all of the variables to be in both data sets and may take a long time if there are many variables.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-02-2015 06:08 PM - edited 12-02-2015 06:12 PM

Just to explain the difference between "except" and "except all":

```
data A;
length X $16;
input X $ Y;
cards;
Apples 1
Oranges 3
Oranges 3
Pears 3
Pears 3
;
data B;
length X $16;
input X $ Y;
cards;
Apples 1
Watermellon 1
Watermellon 1
Oranges 3
Oranges 3
Oranges 3
Oranges 3
Oranges 3
Pears 3
Banana 2
;
proc sql;
create table diff_all as
select * from b
except all
select * from a;
create table diff_wo_all as
select * from b
except
select * from a;
quit;
proc print data=diff_all;
run;
proc print data=diff_wo_all;
run;
```

Using the EXCEPT operator without the ALL option, the multiplicities of (possible) duplicate observations are disregarded. In the above example (with duplicates in A and B) this means that DIFF_WO_ALL will be the same (up to sort order) as DIFF in my first reply. Dataset DIFF_ALL, however, contains

- 1 - 1 = 0 "Apples 1" observations
- 5 - 2 = 3 "Oranges 3" observations
- 2 - 0 = 2 "Watermellon 1" observations
- max(0, 1-2)=0 "Pears 3" observations (not a negative number, of course)
- 1 - 0 = 1 "Banana 2" observation

as shown below.

Obs X Y 1 Banana 2 2 Oranges 3 3 Oranges 3 4 Oranges 3 5 Watermellon 1 6 Watermellon 1

So, if your datasets may contain duplicate records (not necessarily as consecutive records), you have to decide if you want to take the multiplicities into account as in the above example. Either way, as you can see in the example, in the presence of duplicates the number of observations in the resulting dataset is possibly not equal to the difference "no. of observations in B minus no. of observations in A": 10 - 5 equals neither 2 (DIFF_WO_ALL) nor 6 (DIFF_ALL).

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2015 08:48 AM - edited 12-03-2015 08:50 AM

Datastep gives you more options, like if a and b, if b and not a, if a and not b but requires a sort first. Here is an example with datastep merge and proc sql subquery:

data a;

input x$ y;

cards;

apples 1

oranges 2

pears 3

;

data b;

input x$ y;

cards;

apples 1

watermelon 1

oranges 3

pears 3

banana 2

;

proc sort data=a;by x y;

proc sort data=b;by x y;

data want;

merge a (in=a)

b (in=b);

by x;

if b and not a;

run;

proc sql;

create table want_sql as

select x,y

from b

where x not in (select x from a);

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2015 09:48 AM

@Steelers_In_DC: It should be noted that both of your suggestions are not equivalent to the other solutions presented: The task was to select those *observations* from B which are not contained in A. This could very well require to distinguish between, say, (X="Banana", Y=**1**) and (X="Banana", Y=**2**). Your suggestions, however, assume that X alone identifies an observation, so that (X="Banana", Y=**2**) would not be selected from B if (X="Banana", Y=**1**) was contained in A.

Duplicates would be handled differently as well. In case of duplicate values of X in both datasets, let alone duplicate *observations*, the data step would trigger the log message "NOTE: MERGE statement has more than one data set with repeats of BY values.", which would make the alarm bells ring.

With datasets A and B having unique X values and Y values which are determined by X (like @ErinRoberts's original sample data) all four solutions presented would lead to the same set of observations (not identically sorted, though).

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2015 01:19 PM

Here's one more way of doing it using a subquery with the SQL procedure.

```
data dataA;
length X $20;
input X$ Y;
datalines;
Apples 1
Oranges 3
Pears 3
;
run;
data dataB;
length X $20;
input X$ Y;
datalines;
Apples 1
Watermellon 1
Oranges 3
Pears 3
Banana 2
;
run;
proc sql;
create table want as
select *
from dataB
Where X not in (select X from dataA);
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-04-2015 04:08 AM

Dear,

You can try many ways to get it done...

As @dcruik Proc sql is good to use and we can use datastep as well for the same...

try below code ....

```
data A;
input X $ Y;
cards;
Apples 1
Oranges 3
Pears 3
;
run;
data b;
input X $ Y;
cards;
Apples 1
Watermellon 1
Oranges 3
Pears 3
Banana 2
;
run;
/*method 1*/
proc sql;
create table want as select * from b where x not in (select distinct x from a);
quit;
/*Method two */
Proc sort data=a; by x;run;
Proc sort data=b; by x;run;
data want;
merge a(in=_x) b(in=_y);
by x;
if _y and not _x;
run;
```