proc sql exception intesection

[rajeshm]

PROC SQL;
CREATE TABLE onlyfrist AS
SELECT * FROM first
EXCEPT
SELECT * FROM second;
quit;

PROC SQL;
CREATE TABLE onlysecond AS
SELECT * FROM second
EXCEPT
SELECT * FROM first ;
quit;

PROC SQL;
CREATE TABLE firstAndsecond AS
SELECT * FROM first
intersect
SELECT * FROM second;
quit;

my first and second contains 284 records each.

onlyfirst--281

intersect--0

onlysecond--281.  How it is not able to understand. can somebody help me?

Note:  first and second datasets all variables are same including length.

[Kurt_Bremser]

To explain this, we will need to see your data. Please attach the two data steps with datalines as a text file.

[rajeshm]

Thanks for the reply. I can't copy my data here.if you think based on data it is possible to get this type of output,please  provide sample data to suit this output. Don't mind.

data set A=284 records

data set B=284 records

A Except B=281

B Except A=281

A intersect/intersect all B=0

[Kurt_Bremser · Accepted solution]

These two datasets show the effect of duplicates:

data first;
input a $ b;
datalines;
a 1
a 1
b 2
c 3
;

data second;
input a $ b;
datalines;
d 4
d 4
e 5
e 5
;

[Ksharp]

Your first and second should contain duplicated obs .
using ALL to retain all the obs .

SELECT * FROM first
intersect all
SELECT * FROM second;

[rajeshm]

thanks for the reply.

if you think it is possible to get this type of output please provide sample data which can give this type of result.

data set A=284 records

data set B=284 records

A Except B=281

B Except A=281

A intersect/intersect all B=0

[Ksharp]

SELECT * FROM first
EXCEPT ALL
SELECT * FROM second;

[rajeshm]

thanks for the reply boss.

Earlier did not get your answer but now...understand your logic

[rajeshm]

thanks for the reply boss.

did not realize your logic ..but now fine.