topic Re: Selecting a predetermined number of observations from each series of account transactions in SAS Programming

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

cjac — Thu, 22 Oct 2015 13:38:02 GMT

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

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

PaigeMiller — Thu, 22 Oct 2015 13:48:04 GMT

@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;

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

Steelers_In_DC — Thu, 22 Oct 2015 14:03:18 GMT

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;

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

cjac — Thu, 22 Oct 2015 14:43:51 GMT

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!).

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

MikeZdeb — Thu, 22 Oct 2015 15:01:50 GMT

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