CODEWITHSUNDEEP
datesas
Ameya Bhave
Ameya Bhave

Reputation: 107

intck() giving negative value

I am new to SAS and I am having trouble with finding the difference between 2 dates. I have 2 columns: checkin_date and checkout_date the dates are in mmddyy10. format (mm/dd/yyyy).

I have used the following code:

stay_days= intck('day', checkin_day, checkout_day);

I am getting the right values for dates in the same month but wrong values for days that are across 2 months. For example, the difference between 02/06/2014 and 02/11/2014 is 5. But the difference between 1/31/2014 and 2/13/2014 is -18 which is incorrect.

I have also simply tried to subtract them both:

stay_day = checkout_day - checkin_day;

I am getting the same result for that too.

My entire code:

    data hotel;
infile "XXXX\Hotel.dat";
input room_no num_guests checkin_month checkin_day checkin_year checkout_month checkout_day checkout_year internet_used $ days_used room_type $16. room_rate;
checkin_date = mdy(checkin_month,checkin_day,checkin_year);
informat checkin_date mmddyy.;
format checkin_date mmddyy10.;
checkout_date = mdy(checkout_month,checkout_day,checkout_year);
informat checkout_date mmddyy.;
format checkout_date mmddyy10.;
stay_day= intck('day', checkin_day, checkout_day);

Upvotes: 0

Views: 1174

Answers (2)

SunnyRJ
SunnyRJ

Reputation: 393

Your problem is a typo - using wrong variables in intck() function. You are using variables "xxx_DAY" which is the DAY of month instead of the full DATE. Change to stay_day= intck('day', checkin_date, checkout_date);

Upvotes: 2

Tom
Tom

Reputation: 51566

Your data probably has the date values in the wrong variables. When using subtraction the order should be ENDDATE - STARTDATE. When using INTNX() function the order should be from STARTDATE to ENDDATE. In either case if the value in the STARTDATE variable is AFTER the value in the ENDDATE variable then the difference will be a negative number.

Perhaps you need to clean the data?

The only way to get -18 comparing 2014-01-31 and 2014-02-13 would be if you extracted the day of the month and subtracted them.

diff3 = day(end) - day(start);

which would be the same as subtracting 31 from 13.

Example using your dates:

data check;
  input start end ;
  informat start end mmddyy.;
  format start end yymmdd10.;
  diff1=intck('day',start,end);
  diff2=end-start;
cards;
02/06/2014 02/11/2014
1/31/2014  2/13/2014 
;

Results:

Obs         start           end    diff1    diff2

 1     2014-02-06    2014-02-11       5        5
 2     2014-01-31    2014-02-13      13       13

Upvotes: 1

Related Questions