Date:         Tue, 11 May 2004 13:45:18 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Comparing datasets
Comments: To: "SAS_L@LISTSERV.EDU.AU" <SAS_L@LISTSERV.EDU.AU>
Content-Type: text/plain

Pinch-hitting for Ian, I'll throw out two solutions that he has posted in
the past. In a join-optimized SQL implementation, such as SAS SQL, the first
generally works faster. The second solution takes advantage of the fact that
it is operating on single column lists (although it will work equally well
on multicolumn lists, but not on tables with keys):
data test;
  input list;
cards;
3
1
4
8
2
2
3
;
run;
data except;
  input list;
cards;
3
4
2
2
3
;
run;
/*generally most efficient*/
proc sql;
  create table subset as
  select t1.list as list
  from test as t1 left join except as t2
  on t1.list=t2.list
  where t2.list is missing
  ;
quit;
/* List-set processing. */
proc sql;
  create table subset as
  select list from test except select list from except
  ;
quit;
-----Original Message-----
From: Laughing Beggar [mailto:laughing_beggar@HOTMAIL.COM]
Sent: Monday, May 10, 2004 8:48 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Comparing datasets


Hi all,
This is probably dead easy, and I'm almost embarrassed to ask ... I have 2
datasets, each has one variable and Dataset 'B" is a subset of Dataset 'A'.
How do I create a new dataset containing the values in 'A' that are not in
'B' ? I had a go with PROC COMPARE but..... Cheers Laughing_Beggar

"The beggar laughs in the face of the thief"

_________________________________________________________________
Get Extra Storage in 10MB, 25MB, 50MB and 100MB options now! Go to
http://join.msn.com/?pgmarket=en-au&page=hotmail/es2