topic Re: Create 3 separate dates from one date variable using a condition in SAS Programming

Create 3 separate dates from one date variable using a condition

nab102 — Mon, 06 Jun 2022 14:50:25 GMT

Hi ,

This is the dataset I have . I have one date of which a book was purchased. Each customer has a unique Id (shown as customer_id below) . So for each customer have a purchase date. I want to separate the purchase date into three separate group.

This is what I want. For each customer Id, the first date is the same as the purchase_date, so everyone has a purchase date. And their subsequent dates after the purchase is recording in second date and third if they have any. So for example custome_id T002 made purchases twice, one on 09/05/2019 which will be the first date and the second purchase was on 05/07/2021 that's the second date . Since this particular customer doesn't make a third purchase, third_date is recorded as NA.

Re: Create 3 separate dates from one date variable using a condition

PaigeMiller — Mon, 06 Jun 2022 15:25:05 GMT

How is it that customer T007 has no 2nd date but does have a 3rd date? That seems illogical.

Re: Create 3 separate dates from one date variable using a condition

Reeza — Mon, 06 Jun 2022 17:12:11 GMT

1. Sort the data by Customer ID and Purchase Date

proc sort data=have;
by Customer_ID purchase_date;
run;

2. Add a number to indicate the purchase order

data purchaseOrder;
set have;
by Customer_ID purchase_date;
if first.customer_id then order=1;
else order+1;
run;

3. Transpose it to a wide format

proc transpose data=purchaseOrder out=purchaseWide prefix=Purchase;
by CustomerID;
id order;
var purchase_date;
run;

Your columns will be called Purchase1, Purchase2, Purchase3 instead of first_date, second_date, etc.

@nab102 wrote:

Hi ,

This is the dataset I have . I have one date of which a book was purchased. Each customer has a unique Id (shown as customer_id below) . So for each customer have a purchase date. I want to separate the purchase date into three separate group.

This is what I want. For each customer Id, the first date is the same as the purchase_date, so everyone has a purchase date. And their subsequent dates after the purchase is recording in second date and third if they have any. So for example custome_id T002 made purchases twice, one on 09/05/2019 which will be the first date and the second purchase was on 05/07/2021 that's the second date . Since this particular customer doesn't make a third purchase, third_date is recorded as NA.

Re: Create 3 separate dates from one date variable using a condition

nab102 — Mon, 06 Jun 2022 18:15:24 GMT

It's an error

Re: Create 3 separate dates from one date variable using a condition

Tom — Mon, 06 Jun 2022 18:26:32 GMT

You really don't need to add the ORDER variable.

PROC TRANSPOSE will number the variables sequentially automatically.

proc transpose data=have out=purchaseWide prefix=Purchase;
  by CustomerID;
  var purchase_date;
run;

Re: Create 3 separate dates from one date variable using a condition

Reeza — Mon, 06 Jun 2022 19:17:49 GMT

Realizing this doesn't match your output. You have your lines repeated, I only have the final line so this may not be what you need.