03-16-2018 03:38 PM
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.
03-16-2018 04:19 PM
If your date is actually a date value (very highly recommended) and a character then it is as simple as:
by account; /* assumes the data is sorted by account*/
if first.account then firstacct=0;
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.
03-17-2018 08:30 AM
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.
03-19-2018 07:02 PM
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;