Selecting a predetermined number of observations from each series of account transactions

[cjac]

Hello:

Having problems trying to select a max number of records from a series of transactions for the same account #.  Incoming data looks like the following:

Acct#  Name  Spend  Date
123    Bill          $1      Oct1
123    Bill          $3     Oct3
123    Bill          $5     Oct4
123    Bill          $1    Oct4
123    Bill          $4     Oct5
456    Mary      $1     Oct2
456    Mary      $2     Oct2
789    Fred      $3     Oct5
789    Fred      $3     Oct7
789    Fred      $3     Oct8
789    Fred      $3     Oct6
789    Fred      $3     Oct1
999    Jane      $2     Oct9

To simplify matters the outcome should contain up to 3 records (any 3 records) for each acct# (example below).  I am thinking a do loop is required but after pulling my hair out I cannot get the desired result.  Appreciate any suggestions.

Acct#  Name  Spend  Date

123    Bill         $3   Oct3
123    Bill        $1    Oct4
123    Bill        $4   Oct5
456    Mary     $1    Oct2
456    Mary     $2   Oct2
789    Fred      $3   Oct5
789    Fred      $3   Oct7
789    Fred      $3   Oct8
999    Jane      $2   Oct9

[PaigeMiller · Accepted solution]

---

@cjac wrote:

To simplify matters the outcome should contain up to 3 records (any 3 records) for each acct# (example below).  I am thinking a do loop is required but after pulling my hair out I cannot get the desired result.  Appreciate any suggestions.

You can't write a do loop by pulling your hair out -- wink wink

Anyway, this is untested code, should work properly as long as the data is sorted by Acct

data new;
    set whatever;
    by acct;
    if first.acct then count=0;
    count+1;
    if count>3 then delete;
run;

[Steelers_In_DC]

Here is a solution:

data have;
input Acct$  Name$  Spend$  Date$;
cards ;
123    Bill      $1     Oct1
123    Bill      $3     Oct3
123    Bill      $5     Oct4
123    Bill      $1     Oct4
123    Bill      $4     Oct5
456    Mary      $1     Oct2
456    Mary      $2     Oct2
789    Fred      $3     Oct5
789    Fred      $3     Oct7
789    Fred      $3     Oct8
789    Fred      $3     Oct6
789    Fred      $3     Oct1
999    Jane      $2     Oct9
;

DATA WANT;
set have;
by acct;
count + 1;
if first.acct then count = 1;
if count < 4 then output;
drop count;
run;

[cjac]

WOW!!  You guys/gals are GOOD!  Thanks for the quick turnaround.

Paige:

And I cannot afford to lose any more hair.  I'm already "folliclely" challenged (sigh!).

[MikeZdeb]

Hi, another idea ...

data want (drop=count);
set have;
by acct;
count + 1 - (first.acct*count);
if count le 3;
run;

Tips:Between and Within Group Counters

http://www.sascommunity.org/wiki/Tips:Between_and_Within_Group_Counters