topic Re: Distinct Value for one column in SAS Programming

Distinct Value for one column

Kalai2008 — Wed, 13 Feb 2019 03:06:44 GMT

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;

Re: Distinct Value for one column

novinosrin — Wed, 13 Feb 2019 03:35:53 GMT

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;

Re: Distinct Value for one column

ChrisNZ — Wed, 13 Feb 2019 03:38:19 GMT

Since you have no duplicates, this should work:

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

Re: Distinct Value for one column

Tom — Wed, 13 Feb 2019 03:39:24 GMT

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;

Re: Distinct Value for one column

Reeza — Wed, 13 Feb 2019 03:52:45 GMT

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

Re: Distinct Value for one column

novinosrin — Wed, 13 Feb 2019 03:54:11 GMT

Id has to have only north 🙂

You must be tired mam

Re: Distinct Value for one column

Kalai2008 — Wed, 13 Feb 2019 03:59:35 GMT

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

Re: Distinct Value for one column

Reeza — Wed, 13 Feb 2019 04:00:11 GMT

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

Re: Distinct Value for one column

novinosrin — Wed, 13 Feb 2019 04:07:13 GMT

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

Re: Distinct Value for one column

Kalai2008 — Wed, 13 Feb 2019 04:25:14 GMT

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.

Re: Distinct Value for one column

Kalai2008 — Wed, 13 Feb 2019 04:26:03 GMT

Thank you