topic Re: Cumulative counts of numbers in SAS Programming

Cumulative counts of numbers

rkepple — Wed, 14 Jun 2023 16:33:37 GMT

Hi all,

I am looking for a way to cumulatively count variable instances. In the below dataset i want to count the first instance of each coupon 1

then the next time we see the same coupon then 2 and so forth. For example: Jess 4000 should be 2, since Carl 4000 should be 1, and Amii 4000 4. THANKS

data have;
input customer $ customer_id coupon coupon_value;
datalines;
carl 1 4000 9
carl 1 4001 7
carl 1 4002 4
carl 1 4003 3
jess 2 4000 9
jess 2 4004 7
jess 2 4002 5
jess 2 4001 3
lucy 3 4004 8
lucy 3 4000 6
lucy 3 4002 5
lucy 3 4008 2
amii 4 4004 8
amii 4 4000 7
amii 4 4001 4
amii 4 4002 3
;
run;

Re: Cumulative counts of numbers

rkepple — Wed, 14 Jun 2023 16:39:04 GMT

AND I need to retain the order need it sorted / counted by customer ID (1 --> n) THANKS

Re: Cumulative counts of numbers

Tom — Wed, 14 Jun 2023 16:41:56 GMT

The simple way to count like that it to order the data by the grouping variable and then just count them.

But you don't seem to have a variable to indicate the original order.

Perhaps you can just add one.

data for_analysis;
  set have;
  row+1;
run;
proc sort data=for_analysis;
  by coupon row;
run;
data want;
  set for_analysis;
  by coupon;
  count+1;
  if first.coupon then count=1;
run;
proc sort data=want;
  by row;
run;

Re: Cumulative counts of numbers

Tom — Wed, 14 Jun 2023 16:51:09 GMT

The more complicated way is to use a HASH object to store the counter for each coupon.

data want;
  if _n_=1 then do;
     declare hash h();
     h.definekey('coupon');
     h.definedata('count');
     h.definedone();
  end;
  set have;
  if h.find() then count=1;
  else count+1;
  h.replace();
run;

Result

                   customer_              coupon_
Obs    customer        id       coupon     value     count

  1      carl          1         4000        9         1
  2      carl          1         4001        7         1
  3      carl          1         4002        4         1
  4      carl          1         4003        3         1
  5      jess          2         4000        9         2
  6      jess          2         4004        7         1
  7      jess          2         4002        5         2
  8      jess          2         4001        3         2
  9      lucy          3         4004        8         2
 10      lucy          3         4000        6         3
 11      lucy          3         4002        5         3
 12      lucy          3         4008        2         1
 13      amii          4         4004        8         3
 14      amii          4         4000        7         4
 15      amii          4         4001        4         3
 16      amii          4         4002        3         4

Re: Cumulative counts of numbers

ballardw — Wed, 14 Jun 2023 20:29:48 GMT

How many total Coupon identification numbers are there?

Retain a bunch of elements in an array to hold counts.

data want;
   set have;
   array c (4000:4008) _temporary_;
   c[coupon]+1;
   count=c[Coupon];
run;

This does require knowing the number of coupons, that the coupon variable is indeed numeric.

Re: Cumulative counts of numbers

FreelanceReinh — Wed, 14 Jun 2023 20:22:34 GMT

Hi @rkepple,

So you like hash object solutions? Here's another one, just for fun:

data want;
if _n_=1 then do;
  dcl hash h(suminc:'i');
  h.definekey('coupon');
  h.definedone();
end;
set have(in=i);
h.ref();
h.sum(sum:count);
run;