Filter data on a variable & unique observations

[dhir]

Hi, In the attached sample data set I have added the input & output data required. I tried to use nodupkey for unique records but did not get the desired output.

proc sort data = sasuser.sample nodupkey out=sasuser.sample2;
by number;
where region contains 'A';
run;

Only first 3 rows of required output were displayed instead of all 6 rows. 

Thanks in Advance. 

[Shmuel · Accepted solution]

Do it with sql:

proc sql;
   create table want as select * 
   from sasuser.sample where number in
       (select distinct number 
         from sasuser.sample 
         where region = "A") ; quit; 

[dhir]

Thank you, Shmuel. works perfectly.

[mkeintz]

@Shmuel's suggestion is the most compact.  But if this is really about how to use proc sort for your purpose, then you should (1) sort ALL the records number, not just the 'A' records, and (2) read back the sorted file, with the 'A' record preceding non-'A' records for each number, and (3) keep the desired records:

[editted on Wed 7/5 - there is no IF function.  I should have used IFN function, corrected below]

proc sort data=have out=need;

  by number;

run;

data want;

  set have (where=(region='A'))   have (where=(region^='A'));

  by number;

  if first.number then keeprec=ifn(region='A',1,0);

  retain keeprec;

  if keeprec;

run;

[dhir]

Hi mkeintz,

Thank you for the reply.

First part of code worked, need created but second part returned the attached error. 

I have changed the file names in your code: 

proc sort data=sasuser.sample out=sasuser.need;
by number;
run;

data want;
set sasuser.need (where=(region='A')) sasuser.need (where=(region^='A'));
by number;
if first.number then keeprec=if(region='A',1,0);
retain keeprec;
if keeprec;
run;

Help me correct any mistakes here. 

Thank you.