Date:         Wed, 15 Jun 2011 11:12:01 -0400
Reply-To:     Richard DeVenezia <rdevenezia@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Richard DeVenezia <rdevenezia@GMAIL.COM>
Subject:      Determine % of ids in one list not in another
Content-Type: text/plain; charset=ISO-8859-1

Hi:

I am looking for comment on which 'way' might be more efficient or
effective for the following problem:

Given tables LEFT and RIGHT determine the % of ids in RIGHT that are not in LEFT

Way1 uses EXCEPT and Way2 uses RIGHT JOIN.

Sample code:
--------------------------
data left right;
  do id = 1 to 1000;
    do _n_ = 0 to 3 * ranuni(123);
      if ranuni (123) < 0.75 then output right;
    end;
    do _n_ = 0 to 3 * ranuni(123);
      if ranuni (123) < 0.75 then output left;
    end;
  end;
run;

proc sql;
  create table PctUniqueRightNotLeft_way1 as
  select
    nUniqueIdRight
  , nUniqueIdRightNotLeft
  , nUniqueIdRightNotLeft / nUniqueIdRight * 100 as PctUniqueIdRightNotLeft
  from
  ( select count(distinct id) as nUniqueIdRightNotLeft
    from
    (
      select id from RIGHT
      except
      select id from LEFT
    )
  )
  ,
  ( select count(distinct id) as nUniqueIdRight
    from
    RIGHT
  )
  ;

  create table PctUniqueRightNotLeft_way2 as
  select
    count(*) as nUniqueIdRight
  , sum (left_id = .) as nUniqueIdRightNotLeft
  , calculated nUniqueIdRightNotLeft / calculated nUniqueIdRight * 100
as PctUniqueIdRightNotLeft
  from
  (
    select distinct
      left.id as left_id
    , right.id as right_id
    from
      LEFT
    right join
      RIGHT
    on
      LEFT.id = RIGHT.id
  )
  ;
quit;
--------------------------

Thanks,
Richard A. DeVenezia