Filter data on a variable & unique observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Filter data on a variable & unique observations

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. 

 

 

 


Accepted Solutions
Solution
‎07-02-2017 07:44 AM
Trusted Advisor
Posts: 1,554

Re: Filter data on a variable & unique observations

[ Edited ]

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;

 

 

View solution in original post


All Replies
Solution
‎07-02-2017 07:44 AM
Trusted Advisor
Posts: 1,554

Re: Filter data on a variable & unique observations

[ Edited ]

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;

 

 

Occasional Contributor
Posts: 17

Re: Filter data on a variable & unique observations

Thank you, Shmuel. works perfectly.

Trusted Advisor
Posts: 1,018

Re: Filter data on a variable & unique observations

[ Edited ]

@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;

 

Occasional Contributor
Posts: 17

Re: Filter data on a variable & unique observations

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.

 

 

 


1.png
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 266 views
  • 2 likes
  • 3 in conversation