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
@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;
@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;
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;
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!).
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.