BookmarkSubscribeRSS Feed
OzoneX15
Calcite | Level 5
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
5 REPLIES 5
OzoneX15
Calcite | Level 5
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?
Ksharp
Super User
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
DBailey
Lapis Lazuli | Level 10
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.
OzoneX15
Calcite | Level 5
Thanks,

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

Kind regards,

Stefaan
Rockers
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3893 views
  • 0 likes
  • 4 in conversation