turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Filter dataset

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-11-2011 07:51 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to OzoneX15

06-11-2011 08:37 AM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to OzoneX15

06-12-2011 11:28 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to OzoneX15

06-13-2011 07:55 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to OzoneX15

06-13-2011 05:01 PM

Thanks,

It works now.

I thought Sas would have a specific filter for this.

Kind regards,

Stefaan

It works now.

I thought Sas would have a specific filter for this.

Kind regards,

Stefaan

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to OzoneX15

06-14-2011 12:44 AM

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.

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.