```Date: Wed, 15 Jun 2011 11:12:01 -0400 Reply-To: Richard DeVenezia Sender: "SAS(r) Discussion" From: Richard DeVenezia 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 ```

Back to: Top of message | Previous page | Main SAS-L page