topic Re: Filter data on a variable & unique observations in SAS Programming

Filter data on a variable & unique observations

dhir — Sat, 01 Jul 2017 14:36:52 GMT

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.

Re: Filter data on a variable & unique observations

Shmuel — Sat, 01 Jul 2017 18:14:34 GMT

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;

Re: Filter data on a variable & unique observations

mkeintz — Thu, 06 Jul 2017 01:53:04 GMT

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

Re: Filter data on a variable & unique observations

dhir — Sun, 02 Jul 2017 11:38:18 GMT

Thank you, Shmuel. works perfectly.

Re: Filter data on a variable & unique observations

dhir — Sun, 02 Jul 2017 11:49:33 GMT

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.