BookmarkSubscribeRSS Feed
mac91red
Calcite | Level 5

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.

 

 

 

accountdatetimebypassFirstacct
555551-Mar1:00:00 AM511
555552-Mar1:30:00 AM510
555553-Mar1:30:00 AM521
555553-Mar1:45:00 AM520
666661-Jan2:10:00 AM531
666663-Jan2:10:00 AM530
666661-Jan2:10:00 AM541
777771-Feb3:00:00 AM511
777771-Feb3:15:00 AM510
777773-Feb3:30:00 AM510
777775-Feb3:45:00 AM510
3 REPLIES 3
ballardw
Super User

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.

mac91red
Calcite | Level 5

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. 

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 643 views
  • 0 likes
  • 2 in conversation