Help using Base SAS procedures

Subsetting

Reply
Contributor
Posts: 52

Subsetting

Hi

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

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!

Respected Advisor
Posts: 4,644

Re: Subsetting

Why is co_name=3 excluded?

PG
Contributor
Posts: 52

Re: Subsetting

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

Respected Advisor
Posts: 4,644

Re: Subsetting

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;

PG

PG
Contributor
Posts: 52

Re: Subsetting

Thanks Hai.kuo and PGStats Smiley Happy

Respected Advisor
Posts: 3,124

Re: Subsetting

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

Ask a Question
Discussion stats
  • 5 replies
  • 290 views
  • 0 likes
  • 3 in conversation