topic Re: Numbering the nth occurrence in SAS Programming

Numbering the nth occurrence

SURIM — Tue, 16 Oct 2018 05:29:23 GMT

Hi I was wondering how to order the following data into a new column.

ID ORDERID DATE

1000 1098 2018-05-22

1000 1756 2018-05-29

1000 1965 2018-07-22

1001 1145 2018-06-14

1001 1456 2018-08-12

1002 1468 2018-05-28

1003 1548 2018-09-03

1004 1007 2018-06-01

1004 1789 2018-07-27

What I need is a new purchase count column to tell me the 1st, 2nd etc purchase from each ID.

ID ORDERID DATE No of purchase

1000 1098 2018-05-22 1

1000 1756 2018-05-29 2

1000 1965 2018-07-22 3

1001 1145 2018-06-14 1

1001 1456 2018-08-12 2

1002 1468 2018-05-28 1

1003 1548 2018-09-03 1

1004 1007 2018-06-01 1

1004 1789 2018-07-27 2

Thanks!

Re: Numbering the nth occurrence

andreas_lds — Tue, 16 Oct 2018 05:55:49 GMT

Sort data by id, then use a datastep with group processing and retain/reset "NoOfPurchase".

If you want code, post data as datastep using datalines statement.

Re: Numbering the nth occurrence

PeterClemmensen — Tue, 16 Oct 2018 06:29:23 GMT

data have;
input ID $ ORDERID $ DATE:yymmdd10.;
format DATE yymmdd10.;
datalines;
1000 1098 2018-05-22
1000 1756 2018-05-29
1000 1965 2018-07-22
1001 1145 2018-06-14
1001 1456 2018-08-12
1002 1468 2018-05-28
1003 1548 2018-09-03
1004 1007 2018-06-01
1004 1789 2018-07-27
;

data want;
   set have;
   by ID;
   No_of_purchase+1;
   if first.ID then No_of_purchase=1;
   retain No_of_purchase;
run;

Re: Numbering the nth occurrence

SURIM — Tue, 16 Oct 2018 07:48:56 GMT

draycut - Ah, yes thank you! This is fantastic, just what I wanted! You are the most awesome person in the room today.