DATA Step, Macro, Functions and more

The difference between two dates by row and then transpose

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

The difference between two dates by row and then transpose

Hi,

I’m trying to get the difference between dates by row. 

Here is my data set:

data have;

input CUSTOMER_ID date anydtdte23.;

format date date9.;

cards;

1057086 21AUG2014

1057086 25AUG2014

1057086 17SEP2014

1057086 17SEP2014

1057086 19SEP2014

1057086 26SEP2014

;


And this is the program I have so far:

data want;

set have;

days_between = intck('days',date,lag(date)); run;


My results look like this :

CUSTOMER_IDdatedays_between
105708621-Aug-14.
105708625-Aug-14-4
105708617-Sep-14-23
105708619-Sep-14-2
105708626-Sep-14-7


What I need is the results to be positive values


CUSTOMER_IDdatedays_between
105708621-Aug-14.
105708625-Aug-144
105708617-Sep-1423
105708619-Sep-142
105708626-Sep-147


And after that I need to transpose the values so they look like this:


CUSTOMER_IDFirst_Purchase DatePurchase_2Purchase_3Purchase_4Purchase_5
105708621-Aug-1442327


Notice I have kept the first purchase date. 


Any help will be greatly appreciated. Thanks!





Accepted Solutions
Solution
‎06-26-2015 07:46 AM
Contributor
Posts: 21

Re: The difference between two dates by row and then transpose

Posted in reply to RobertNYC

Hi Robert,

I tried to reconstruct your program. See if below will help you.

data have;

  input CUSTOMER_ID date anydtdte23.;

  format date date9.;

cards;

1057086 21AUG2014

1057086 25AUG2014

1057086 17SEP2014

1057086 17SEP2014

1057086 19SEP2014

1057086 26SEP2014

;

proc sort; by customer_id date; run;

data nhave;

  set have;

  by customer_id date;

  if first.customer_id then output;

run;

data wantz;

  if _N_ = 1 then set nhave(rename=(date = date1));

  set have;

  days_between = dif(date);

  drop date;

  rename date1 = date;

run;

proc transpose data = wantz out = nwant(drop = _name_ Purchase_1) prefix = Purchase_;

  by customer_id date;

  var days_between;

run;

Br,Amit

View solution in original post


All Replies
Super Contributor
Super Contributor
Posts: 3,174

Re: The difference between two dates by row and then transpose

Posted in reply to RobertNYC

Change your INTCK argument order for the two dates and the results will go positive.  Then have a look at using PROC TRANSPOSE in the SAS PROCEDURES documentation / examples provided.

Scott Barry

SBBWorks, Inc.

Frequent Contributor
Posts: 101

Re: The difference between two dates by row and then transpose

Thanks Sbb,

I'm good with the transposing its just keeping/ not transposing the First_Purchase Date that I can't figure out.

Super User
Posts: 11,343

Re: The difference between two dates by row and then transpose

Posted in reply to RobertNYC

data set options can be your friend:

proc transpose data=have

      out=want (drop=_name_ purchase_1 rename=(date=First_purchase_date) where=(Purchase_2 ne .))

      prefix=Purchase_;

by customer_id;

copy date;

var days_between;

run;

I won't claim efficiency other than code isn't complex

Super Contributor
Super Contributor
Posts: 3,174

Re: The difference between two dates by row and then transpose

Posted in reply to RobertNYC

Also, if you intend to have multiple Customer_ID values, the data should be sorted, and you will need BY CUSTOMER_ID;   with some additional IF FIRST.CUSTOMER_ID THEN ....;   code to conditionally use the LAG(date) variable result/value.

Also, the consideration for having PURCHASE_n variables with/using multiple output formats will need to be addressed with any TRANSPOSE-output file unless you do capture / maintain First_Purchase_Date separately -- possibly it could be that PURCHASE_1 would use DATE9 format and other PURCHASE_n variables having no output FORMAT defined.

Scott Barry
SBBWorks, Inc.

Occasional Contributor
Posts: 9

Re: The difference between two dates by row and then transpose

Posted in reply to RobertNYC

If you want it to be positive just use abs (absolute difference).

Solution
‎06-26-2015 07:46 AM
Contributor
Posts: 21

Re: The difference between two dates by row and then transpose

Posted in reply to RobertNYC

Hi Robert,

I tried to reconstruct your program. See if below will help you.

data have;

  input CUSTOMER_ID date anydtdte23.;

  format date date9.;

cards;

1057086 21AUG2014

1057086 25AUG2014

1057086 17SEP2014

1057086 17SEP2014

1057086 19SEP2014

1057086 26SEP2014

;

proc sort; by customer_id date; run;

data nhave;

  set have;

  by customer_id date;

  if first.customer_id then output;

run;

data wantz;

  if _N_ = 1 then set nhave(rename=(date = date1));

  set have;

  days_between = dif(date);

  drop date;

  rename date1 = date;

run;

proc transpose data = wantz out = nwant(drop = _name_ Purchase_1) prefix = Purchase_;

  by customer_id date;

  var days_between;

run;

Br,Amit

Super User
Posts: 10,018

Re: The difference between two dates by row and then transpose

Posted in reply to RobertNYC

I am a little surprise . You didn't use DIF().

Code: Program

data have;
input CUSTOMER_ID date anydtdte23.;
format date date9.;
cards;
1057086 21AUG2014
1057086 25AUG2014
1057086 17SEP2014
1057086 17SEP2014
1057086 19SEP2014
1057086 26SEP2014
;
run;

data temp;
set have;
by CUSTOMER_ID;
length v id $ 20;
days_between=dif(date);
if first.CUSTOMER_ID then do;
   n=1;
   call missing(days_between);
   v=put(date,date11.);
   id='First_Purchase_date';
end;
  else do;
   n+1;
   v=put(days_between,best8.);
   id=cats('Purchase_',n);
   end;
run;
proc transpose data=temp out=want(drop=_:) ;
by CUSTOMER_ID;
var v;
id id;
run;


Contributor
Posts: 21

Re: The difference between two dates by row and then transpose

Mistake. Updated the correct program. I picked the dataset from work.

Thanks, Amit

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 2678 views
  • 8 likes
  • 6 in conversation