topic Re: Difference between the dates depending on the conditions in SAS Programming

Difference between the dates depending on the conditions

Vk_2 — Tue, 12 Mar 2019 23:24:05 GMT

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)

Re: Difference between the dates depending on the conditions

VDD — Wed, 13 Mar 2019 01:29:33 GMT

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

Re: Difference between the dates depending on the conditions

Vk_2 — Wed, 13 Mar 2019 13:05:31 GMT

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

Re: Difference between the dates depending on the conditions

Vk_2 — Wed, 13 Mar 2019 14:27:25 GMT

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.