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!
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;
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.
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;
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.