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;
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
AND I need to retain the order need it sorted / counted by customer ID (1 --> n) THANKS
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;
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
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.
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;
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.