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

I want to calculate days between like this calculation shown picture

I wrote the code like this

DATA table2;
infile datalines DELIMITER=',';
INFORMAT cust_id 4.  visit_dt  Purchase_dt MMDDYY10. item $10.  first_purchase_dt MMDDYY10. ; 
INPUT cust_id visit_dt Purchase_dt item FIRST_Purchase_dt;  
format visit_dt Purchase_dt FIRST_Purchase_dt date9.;
DATALINES;
1234,01/11/2017,01/12/2017,Big,01/01/2015
1234,01/18/2018,01/19/2018,Big,01/01/2015
1567,01/11/2008,01/12/2008,Big,03/27/2007
1345,01/03/2006,.,Small,01/02/2006
1345,01/24/2008,01/24/2008,Big,01/02/2006
1579,01/24/2009,.,Medium,05/06/2006
;
run;
 
proc sort data=table2;

by cust_id visit_dt ;
run;
data want;
set table2;
by cust_id;
prev_date=lag(COALESCE(Purchase_dt,visit_dt));
format prev_date date9.;
DAYS_BTW = intck('day', first_purchase_dt, visit_dt); 
run;

Days between should be calculated like this-

a) if there is no duplicate Cust_id then it is visit_dt-First_Purchase dt or the first duplicate cust_id when sorted by visit_dt.

b) if there is duplicate then visit_dt-Previous(purchase_dt) if it exists else visit_dt-previous(visit_dt). This happens as when the item is small or medium then there is no purchase dt.

 

I think I am using right functions but not according to the conditions that I want. I am using sas eg version-7.12 (7.100.2.3350) (64-bit)

Capture_days_btw.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Vk_2
Obsidian | Level 7
DATA table2;
infile datalines DELIMITER=',';
INFORMAT cust_id 4.  visit_dt  Purchase_dt MMDDYY10. item $10.  first_purchase_dt MMDDYY10. ; 
INPUT cust_id visit_dt Purchase_dt item FIRST_Purchase_dt;  
format visit_dt Purchase_dt FIRST_Purchase_dt date9.;
DATALINES;
1234,01/11/2017,01/12/2017,Big,01/01/2015
1234,01/18/2018,01/19/2018,Big,01/01/2015
1567,01/11/2008,01/12/2008,Big,03/27/2007
1345,01/03/2006,.,Small,01/02/2006
1345,01/24/2008,01/24/2008,Big,01/02/2006
1579,01/24/2009,.,Medium,05/06/2006
;
run;
 
proc sort data=table2;

by cust_id visit_dt ;
run;
data want;
set table2;
by cust_id;
prev_cust_id=lag(cust_id);
prev_date=lag(COALESCE(Purchase_dt,visit_dt));
format prev_date date9.;
if cust_id=prev_cust_id then DAYS_BTW = intck('day', prev_date,visit_dt);
else DAYS_BTW = intck('day', FIRST_Purchase_dt,visit_dt);
drop prev_cust_id prev_date; run;

got the logic right.

View solution in original post

3 REPLIES 3
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

your screen shot example doesn't match what your output is based on your code sample.

 

The SAS System

Obs cust_id visit_dt Purchase_dt item first_purchase_dt prev_date DAYS_BTW
1 1234 11JAN2017 12JAN2017 Big 01JAN2015 . 741
2 1234 18JAN2018 19JAN2018 Big 01JAN2015 12JAN2017 1113
3 1345 03JAN2006 . Small 02JAN2006 19JAN2018 1
4 1345 24JAN2008 24JAN2008 Big 02JAN2006 03JAN2006 752
5 1567 11JAN2008 12JAN2008 Big 27MAR2007 24JAN2008 290
6 1579 24JAN2009 . Medium 06MAY2006 12JAN2008 994

 

 

 

 

Vk_2
Obsidian | Level 7

I have changed the date format. But the contents are same. I am looking for the conditions to apply the days between calaculations.

Vk_2
Obsidian | Level 7
DATA table2;
infile datalines DELIMITER=',';
INFORMAT cust_id 4.  visit_dt  Purchase_dt MMDDYY10. item $10.  first_purchase_dt MMDDYY10. ; 
INPUT cust_id visit_dt Purchase_dt item FIRST_Purchase_dt;  
format visit_dt Purchase_dt FIRST_Purchase_dt date9.;
DATALINES;
1234,01/11/2017,01/12/2017,Big,01/01/2015
1234,01/18/2018,01/19/2018,Big,01/01/2015
1567,01/11/2008,01/12/2008,Big,03/27/2007
1345,01/03/2006,.,Small,01/02/2006
1345,01/24/2008,01/24/2008,Big,01/02/2006
1579,01/24/2009,.,Medium,05/06/2006
;
run;
 
proc sort data=table2;

by cust_id visit_dt ;
run;
data want;
set table2;
by cust_id;
prev_cust_id=lag(cust_id);
prev_date=lag(COALESCE(Purchase_dt,visit_dt));
format prev_date date9.;
if cust_id=prev_cust_id then DAYS_BTW = intck('day', prev_date,visit_dt);
else DAYS_BTW = intck('day', FIRST_Purchase_dt,visit_dt);
drop prev_cust_id prev_date; run;

got the logic right.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 556 views
  • 0 likes
  • 2 in conversation