Identify unique values between two variables

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Identify unique values between two variables

Dear SAS Community;

I have two variables, A and B, and would like to display the instances of B that do not occur in A.  For example:

A     B

1      1

3      2

4      3

6      4

7      5

10    6

        7

        8

        9

       10

Could someone please suggest some code for me to display my desired output?  Thank you.


Accepted Solutions
Solution
‎09-23-2013 10:35 PM
Respected Advisor
Posts: 4,173

Re: Identify unique values between two variables

A SQL approach:

proc sql;
  create table want as
  select B from mydata o
  where not exists 
    (select A from mydata i where o.b=i.a)
  ;
quit;


View solution in original post


All Replies
Trusted Advisor
Posts: 1,321

Re: Identify unique values between two variables

Hi There,

Using a hash object might help...

data mydata;
     input @1 A $3. @3 B $3.;
cards;
1  1
3  2
4  3
6  4
7  5
10 6
   7
   8
   9
   10
;
run;

data lookup;
     length B $3;
     set mydata(rename=(B=value));
     if _N_=1 then do;
          declare hash ht(dataset:"mydata");
          ht.defineKey("A");
          ht.defineData("B");
          ht.defineDone();
     end;
     rc = ht.find(key:value);
     if rc ne 0 then output;
     keep value;
run;


Cheers,

Michelle

Solution
‎09-23-2013 10:35 PM
Respected Advisor
Posts: 4,173

Re: Identify unique values between two variables

A SQL approach:

proc sql;
  create table want as
  select B from mydata o
  where not exists 
    (select A from mydata i where o.b=i.a)
  ;
quit;


SAS Super FREQ
Posts: 3,752

Re: Identify unique values between two variables

A SAS/IML program:

proc iml;

A = {1,3,4,6,7,10};

B = T(1:10);

k = element(B, A);  /* indicator variable: is B in A? */

BInA = loc(k=1);

BNotInA = loc(k=0);

print BInA, BNotInA;

Contributor
Posts: 44

Re: Identify unique values between two variables

Thank you for your responses! Proc sql worked great. 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 260 views
  • 7 likes
  • 4 in conversation