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;
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;
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;
Since you have no duplicates, this should work:
data WANT;
set HAVE;
by ID;
if DIRECTION = 'North' and first.ID and last.ID;
run;
Thank you
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;
Perfect...You saved my time.. Thank you ..It worked, simple SQL code.
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.
Id has to have only north 🙂
You must be tired mam
@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!
lol That makes two . Just did a microwave instant noodles (lazy & tired). Gonna sleep in a bit. Good night and sleep tight. Take care!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.