topic Re: proc sql exception intesection in SAS Procedures

proc sql exception intesection

rajeshm — Tue, 25 Aug 2020 04:59:14 GMT

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.

Re: proc sql exception intesection

Kurt_Bremser — Tue, 25 Aug 2020 05:54:49 GMT

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

Re: proc sql exception intesection

Ksharp — Tue, 25 Aug 2020 11:59:38 GMT

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

SELECT * FROM first
intersect all
SELECT * FROM second;

Re: proc sql exception intesection

rajeshm — Wed, 26 Aug 2020 05:00:58 GMT

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

Re: proc sql exception intesection

rajeshm — Wed, 26 Aug 2020 05:00:02 GMT

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

Re: proc sql exception intesection

Kurt_Bremser — Wed, 26 Aug 2020 07:10:29 GMT

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
;

Re: proc sql exception intesection

Ksharp — Wed, 26 Aug 2020 11:42:12 GMT

SELECT * FROM first
EXCEPT ALL
SELECT * FROM second;

Re: proc sql exception intesection

rajeshm — Wed, 26 Aug 2020 12:03:24 GMT

thanks for the reply boss.

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

Re: proc sql exception intesection

rajeshm — Wed, 26 Aug 2020 12:05:03 GMT

thanks for the reply boss.

did not realize your logic ..but now fine.