Distinct Value for one column

[Kalai2008]

From the given dataset, I am trying to filter certain values from the Variable.
Example: There are 2 columns, (ID and Direction) , I need to create a new dataset with Variable Direction having value only North. Eg, My code should pick ID 2, 4 only. I don't need the combination. Only ID's having North Direction.

ID Direction
1 South
1 North
1 West
1 East
2 North
3 South
3 North
3 West
4 North
5 South
6 West
6 East

7 South

7 North
data one multi;
length _c $10;
call missing(__c,_f);
do until(last.ID);
set test;
by id direction notsorted;
if _c ne direction then __c+1;
_c=direction;
if direction="NORTH" then _f=1;
end;
do until(last.id);
set TEST;
by id direction notsorted;
if __c=1 then output one;
else output multi

end;
drop _:;
run;

[novinosrin · Accepted solution]

straight forward sql:

data have;
input ID Direction $ ;
cards;
1 South
1 North
1 West
1 East
2 North
3 South
3 North
3 West
4 North
5 South
6 West
6 East
7 South
7 North
;

proc sql;
create table want as
select *
from have 
group by id
having direction='North' and count(distinct direction)=1;
quit;

[ChrisNZ]

Since you have no duplicates, this should work:

data WANT; 
  set HAVE;
  by ID;
  if DIRECTION = 'North' and first.ID and last.ID;
 run;

[Kalai2008]

Thank you

[Tom]

English is a tricky language.

I think that you are asking to find the IDs that only have Direction='North'. 

If your rows are distinct (that is you cannot have the same ID and Direction repeated) then this means you want IDS that have only one observation AND that observation has Direction='North'.

data want ;
  set test;
  by id;
  if first.id and last.id and direction='North';
run;

Perhaps you want something like this?

data north(keep=id) south(keep=id) east(keep=id) west=(keep=id) multi;
  set test;
  by id ;
  if not (first.id and last.id) then output multi;
  else if direction='North' then output north;
  else if direction='South' then output south;
  else if direction='East' then output east;
  else if direction='West' then output west;
run;

[Kalai2008]

Perfect...You saved my time.. Thank you ..It worked, simple SQL code.

[Kalai2008]

Thank you and your code worked . My dataset is very huge with 100 variables, over half a million records. I just gave an example, but in real,the column Direction  have many Values. Rows are not distinct, ID's and Direction repeats itself , having duplicates due to other variables.

[Reeza]

Why isn't ID=3 selected, it has a North value?

[novinosrin]

Id has to have only north 🙂

You must be tired mam

[Reeza]

---

@novinosrin wrote:

Id has to have only north 🙂

You must be tired mam

---

lol, I actually am. Did a full day of work and then presentations to students and finally eating dinner now!

[novinosrin]

lol That makes two . Just did a microwave instant noodles (lazy & tired). Gonna sleep in a bit. Good night and sleep tight. Take care!