DATA Step, Macro, Functions and more

The difference between two dates by row and then transpose

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 99
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

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

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: 99

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: 10,548

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

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

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

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

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: 9,691

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

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
  • 2110 views
  • 8 likes
  • 6 in conversation