BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cjac
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

@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

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

@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
Steelers_In_DC
Barite | Level 11

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
Calcite | Level 5

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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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