topic Subsetting in SAS Procedures

Subsetting

namrata — Mon, 16 Dec 2013 01:10:45 GMT

If this is an easy question I am asking, then my SAS beginner skills are really getting rusty

My dataset is this:

co_name year cost

1 1999 4

1 2000 3

1 2001 1

1 2002 0

1 2003 1

1 2004 1

2 2001 1

2 2002 0

2 2003 1

3 2003 2

3 2004 0

The filter rule is cost=0. However, I not only want the rows where cost=0 but also atleast two rows preceding and two rows following that row...something like this:

co_name year cost

1 2000 3

1 2001 1

1 2002 0

1 2003 1

1 2004 1

2 2001 1

2 2002 0

2 2003 1

Thanks a lot!

Re: Subsetting

PGStats — Mon, 16 Dec 2013 02:10:29 GMT

Why is co_name=3 excluded?

Re: Subsetting

namrata — Mon, 16 Dec 2013 02:16:51 GMT

Ohh!I did not mean to exclude it. Even co_name 3 gets in the subset if it has code=0.

Re: Subsetting

Haikuo — Mon, 16 Dec 2013 02:21:44 GMT

Of course there would be many solutions, while I would take it as a typical 2XDOW scenario.

data have;

input co_name $ year cost;

cards;

1 1999 4

1 2000 3

1 2001 1

1 2002 0

1 2003 1

1 2004 1

2 2001 1

2 2002 0

2 2003 1

3 2003 2

3 2004 0

;

data want;

do _n_=1 by 1 until (last.co_name);

set have;

by co_name notsorted;

if cost=0 then _0=_n_;

end;

do _n_=1 by 1 until (last.co_name);

set have;

by co_name notsorted;

if _0-2 <= _n_ <= _0+2 then output;

end;

drop _0;

run;

Haikuo

Re: Subsetting

PGStats — Mon, 16 Dec 2013 02:45:40 GMT

There is also the SQL solution:

proc sql;

create table want as

select h.*

from

have as h inner join

(select * from have where cost=0) as z

on h.co_name=z.co_name and h.year between z.year-2 and z.year+2

order by co_name, year;

select * from want;

quit;

Re: Subsetting

namrata — Fri, 20 Dec 2013 00:08:29 GMT

Thanks Hai.kuo and PGStats