CODEWITHSUNDEEP
setsas
user3391003
user3391003

Reputation: 3

SAS: Pick out certain observations to display

Obs Best _streak_ 
1 Freeburg Foxes  1 
2 Freeburg Foxes  2 
3 Freeburg Foxes  3 
4 Freeburg Foxes  4 
5 Charlotte Chipmunks  1 
6 Toronto Turtles  1 
7 Toronto Turtles  2 
8 Freeburg Foxes  1 
9 Freeburg Foxes  2 
10 Toronto Turtles  1 
...

Obs Best _streak_ 
1 Freeburg Foxes 4
2 Charlotte Chipmunks  1 
3 Toronto Turtles  2 
4 Freeburg Foxes 2 (thanks for correcting)
...

Above (first one on top) is my current SAS output. However I want to display only the max amount of times a team has been on a streak with team name once. So My output would look like the second one (or the shorter output).

Upvotes: 0

Views: 115

Answers (2)

Longfish
Longfish

Reputation: 7602

If the data is sorted in the order you've specified then you can get your result with just one pass of the data, using the NOTSORTED option.

data have;
input best & $20. _streak_;
datalines;
Freeburg Foxes  1 
Freeburg Foxes  2 
Freeburg Foxes  3 
Freeburg Foxes  4 
Charlotte Chipmunks  1
Toronto Turtles  1 
Toronto Turtles  2 
Freeburg Foxes  1 
Freeburg Foxes  2 
Toronto Turtles  1
;
run;

data want;
set have;
by best notsorted;
if last.best;
run;

Upvotes: 1

Dmitry Shopin
Dmitry Shopin

Reputation: 1763

I believe in the desired output observation 4 should be:

4 Freeburg Foxes 2

? So that we choose the maximum streak for each contingent series of records for each team, not the absolute maximum, right?

Then you can do that like this, adding the second instance of the same dataset, shifted one row up, so that to be able "to look ahead" and decide that current record is the last in a series::

data want;
    set have;
    if not eof then do;
         set have(firstobs=2 keep=Best
                        rename=(Best=nextBest)) 
                        end=eof;
    end;
    if Best^=nextBest or eof then output;
    drop next:;
run;

Upvotes: 0

Related Questions