Help using Base SAS procedures

Filter dataset

Reply
Occasional Contributor
Posts: 14

Filter dataset

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
Occasional Contributor
Posts: 14

Re: Filter dataset

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?
Super User
Posts: 10,048

Re: Filter dataset

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
Super Contributor
Posts: 578

Re: Filter dataset

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.
Occasional Contributor
Posts: 14

Re: Filter dataset

Thanks,

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

Kind regards,

Stefaan
New Contributor
Posts: 4

Re: Filter dataset

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.
Ask a Question
Discussion stats
  • 5 replies
  • 1557 views
  • 0 likes
  • 4 in conversation