topic Re: The difference between two dates by row and then transpose in SAS Programming

The difference between two dates by row and then transpose

RobertNYC — Thu, 25 Jun 2015 16:50:02 GMT

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 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_ID	date	days_between
1057086	21-Aug-14	.
1057086	25-Aug-14	-4
1057086	17-Sep-14	-23
1057086	19-Sep-14	-2
1057086	26-Sep-14	-7

What I need is the results to be positive values

CUSTOMER_ID	date	days_between
1057086	21-Aug-14	.
1057086	25-Aug-14	4
1057086	17-Sep-14	23
1057086	19-Sep-14	2
1057086	26-Sep-14	7

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

CUSTOMER_ID	First_Purchase Date	Purchase_2	Purchase_3	Purchase_4	Purchase_5
1057086	21-Aug-14	4	23	2	7

Notice I have kept the first purchase date.

Any help will be greatly appreciated. Thanks!

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

sbb — Thu, 25 Jun 2015 16:53:20 GMT

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.

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

RobertNYC — Thu, 25 Jun 2015 17:11:39 GMT

Thanks Sbb,

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

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

ballardw — Thu, 25 Jun 2015 18:00:48 GMT

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

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

sbb — Thu, 25 Jun 2015 19:25:49 GMT

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.

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

BartDekeyser — Fri, 26 Jun 2015 08:02:43 GMT

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

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

AmitRathore — Fri, 26 Jun 2015 11:46:51 GMT

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

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

Ksharp — Fri, 26 Jun 2015 13:03:39 GMT

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;

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

AmitRathore — Fri, 26 Jun 2015 15:46:20 GMT

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

Thanks, Amit