DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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
Trusted Advisor
Posts: 1,610

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;

View solution in original post


All Replies
Solution
‎10-22-2015 10:44 AM
Trusted Advisor
Posts: 1,610

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;
Valued Guide
Posts: 858

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

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.

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

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