topic Re: comparing date field in SAS Programming

comparing date field

mac91red — Fri, 16 Mar 2018 19:38:01 GMT

I could use some help comparing the date field in the below example. I used first. to get the first record by account and bypass. What i would like to do is compare the date field. For example, for the account 55555 i'd like the date lag to show 0 for the first observation, and then 1 for the second observation since it is a day later than the first instance for that account number and bypass combination. Observation 3 and 4 for account number 55555 should show a date lag of 0 for both as they share the same date. For account number 77777 id like the first and second record to show 0 as they are the same date, then the third record to show 2, and the final record to show 4. Thank you in advance.

account	date	time	bypass	Firstacct
55555	1-Mar	1:00:00 AM	51	1
55555	2-Mar	1:30:00 AM	51	0
55555	3-Mar	1:30:00 AM	52	1
55555	3-Mar	1:45:00 AM	52	0
66666	1-Jan	2:10:00 AM	53	1
66666	3-Jan	2:10:00 AM	53	0
66666	1-Jan	2:10:00 AM	54	1
77777	1-Feb	3:00:00 AM	51	1
77777	1-Feb	3:15:00 AM	51	0
77777	3-Feb	3:30:00 AM	51	0
77777	5-Feb	3:45:00 AM	51	0

Re: comparing date field

ballardw — Fri, 16 Mar 2018 20:19:26 GMT

If your date is actually a date value (very highly recommended) and a character then it is as simple as:

data want;

set have;

by account; /* assumes the data is sorted by account*/

difdate= dif(date);

if first.account then firstacct=0;

else firstacct=difdate;

drop difdate;

run;

However I am afraid that you "date" may be character and is incomplete as I do not see any year component and there could be an issue the 1-Mar is 1Mar2017 or 1Mar2018 in consecutive records and should not be 0. Year also being an import consideration for 27-Feb to 1-Mar depending on leap years. If you can get a YEAR with that day-Month then create actual date values.

Re: comparing date field

mac91red — Sat, 17 Mar 2018 12:30:54 GMT

Thanks for the reply - my date field is date9 but that is a numeric. Does it have to be alpha? I ran the code and the results weren't exactly what i expected. For example the 77777 example the last observation has a 4 day difference yet the output calculated only a 2 for difdate.

Re: comparing date field

ballardw — Mon, 19 Mar 2018 23:02:16 GMT

1) It helps to provide data in the form of a data step. I did not see anything resembling an actual valid date (no year) so could not attempt to create data set to test code against.

2) It would really help to show the actual result for your example data as I'm not sure what your actual result would be after that comment.

3) if Bypass or FirstAcct play a role in this calculation you need to state it.

Guessing at this point that you want to compare to the date associated with the first value of bypass within account.

data want;
   set have;
   by account bypass;  /* assumes the data is sorted by account and bypase*/
   retain firstdate .;
   if first.account then do;
      firstacct=0;
      firstdate=date;
   end;
   else firstacct= date-firstdate;
   drop firstdate;

run;