topic Re: Filter dataset in SAS Procedures

Filter dataset

OzoneX15 — Sat, 11 Jun 2011 11:51:43 GMT

Dear Sas users,

I have difficulties filtering a dataset;

I only want these observations that have no missing values between day 840 and 845.

For example,

STOCK DATE

A 840
A 841
A 842
A 843
A 844
A 845
B 840
B 841
B 842
C 840
C 841
C 842
C 843
C 844
C 845
D 842
D 843
D 844
D 845

I can only take these stocks that have non missing values between 840 and 845.
So I Cannot select stock C and B. Ans I want a new dataset only with the variables A and D.
How do I use a proper filter technique?

Kind regards,

Stefaan

Message was edited by: OzoneX15 Message was edited by: OzoneX15

Re: Filter dataset

OzoneX15 — Sat, 11 Jun 2011 12:37:30 GMT

I thought to

1) use the filter between 840 and 845
2) Count by stock
3) filter only those who have a count five

but this is not the right way I suppose?

Re: Filter dataset

Ksharp — Mon, 13 Jun 2011 03:28:04 GMT

Hi.
Be honest. I cannot understand what is your logic exactly.
You said So I Cannot select stock C and B. Why? It is because they are all started with date=840 ? Supposing it were.

[pre]

data temp;
input STOCK $ DATE ;
cards;
A 840
A 841
A 842
A 843
A 844
A 845
B 840
B 841
B 842
C 840
C 841
C 842
C 843
C 844
C 845
D 842
D 843
D 844
D 845
;
run;
data temp(where =(count eq 1));
set temp;
if stock ne lag(stock) then do;
if date=840 then count+1;
else count=1;
end;
run;
[/pre]

Ksharp

Re: Filter dataset

DBailey — Mon, 13 Jun 2011 11:55:16 GMT

One way (sort of hard-coded):

proc sql;
create able stocks as
select t0.stock
from
(select * from stocks where date=840) t0
inner join (select * from stocks where date=841) t1 on t0.stock=t1.stock
inner join (select * from stocks where date=842) t2 on t0.stock=t2.stock
inner join (select * from stocks where date=843) t3 on t0.stock=t3.stock
inner join (select * from stocks where date=844) t4 on t0.stock=t4.stock
inner join (select * from stocks where date=845) t5 on t0.stock=t5.stock;
quit;

That's not an optimal solution as it might not be easily adapted to other questions..but it should work.

Re: Filter dataset

OzoneX15 — Mon, 13 Jun 2011 21:01:23 GMT

Thanks,

It works now.
I thought Sas would have a specific filter for this.

Kind regards,

Stefaan

Re: Filter dataset

Rockers — Tue, 14 Jun 2011 04:44:05 GMT

data temp;
input a $ b ;
cards;
A 840
A 841
A 842
A 843
A 844
A 845
B 840
B 841
B 842
C 840
C 841
C 842
C 843
C 844
C 845
D 842
D 843
D 844
D 845
;run;

data temp2(keep=a);
set temp;
by a;
if first.a then counter =1;
else counter +1;
if counter=6 then output;
run;

i am checking if a stock has 6 records if it is so then i am taking that stock to output.. not sure if this is what u wanted.