BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RobertNYC
Obsidian | Level 7

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!




1 ACCEPTED SOLUTION

Accepted Solutions
AmitRathore
Obsidian | Level 7

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

8 REPLIES 8
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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.

RobertNYC
Obsidian | Level 7

Thanks Sbb,

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

ballardw
Super User

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

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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.

BartDekeyser
Calcite | Level 5

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

AmitRathore
Obsidian | Level 7

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

Ksharp
Super User

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;


AmitRathore
Obsidian | Level 7

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

Thanks, Amit

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 10161 views
  • 11 likes
  • 6 in conversation