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

Solved
Occasional Contributor
Posts: 9

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

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

Accepted Solutions
Solution
‎10-22-2015 10:44 AM
Respected Advisor
Posts: 2,989

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

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;``````
--
Paige Miller

All Replies
Solution
‎10-22-2015 10:44 AM
Respected Advisor
Posts: 2,989

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

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;``````
--
Paige Miller
Valued Guide
Posts: 863

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

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;

Occasional Contributor
Posts: 9

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

Posted in reply to Steelers_In_DC

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

Valued Guide
Posts: 765

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 4 replies
• 231 views
• 1 like
• 4 in conversation