How to calculate difference in days from two observations within a BY group?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How to calculate difference in days from two observations within a BY group?

[ Edited ]

Hi SAS Users!

I want to get the differences of dates and kms in a BY group. Below, first.Reg_No should indicate 0 whereas further observartions in the BY group should give the difference in dates as well as kms from the previous observation. I have the data in the form:

 

Reg_No                ID          Job_Card_Dt      KM_reading

MXS3408             100         1-Jan-16            15000

MTV2876             101         1-Jan-16            7000

MTV2876             101         5-Jan-16            9000

MTV2876             101         10-Jan-16          10000

XTP2538              102         1-Mar-16           12000

XTP2538              102         3-Mar-16           4500


And the output should look like: 

 

 

Reg_No                ID           Job_Card_Dt      KM_reading       Days      Kms

MXS3408             100         1-Jan-16             15000                 0               0

MTV2876             101         1-Jan-16              7000                  0               0

MTV2876             101         5-Jan-16              9000                  4              2000

MTV2876             101        10-Jan-16            10000                 5              1000

XTP2538              102         1-Mar-16             12000                0              0

XTP2538              102         3-Mar-16             4500                  2              7500

 

Thank you!


Accepted Solutions
Solution
‎11-10-2016 07:13 AM
Super User
Posts: 5,504

Re: How to calculate difference in days from two observations within a BY group?

The more the merrier?

 

data want;

set have;

by ID;

Kms = dif(KM_reading);

days = dif(Job_Card_Dt);

if first.id then do;

   kms=0;

   days=0;

end;

run;

 

When using DIF or LAG, it's important (for at least 99% of applications) to make sure they execute on each observation.  I believe that IFN handles this properly, but would test it to make sure if that is your tool of choice.

View solution in original post


All Replies
Super User
Super User
Posts: 7,955

Re: How to calculate difference in days from two observations within a BY group?

Not tested this, post test data in the form of a datastep if you want working code, I am guessing on your data structure:

data want;
  set have;
  by reg_no;
  if first.reg_no then do;
    days=0;
    kms=0;
  end;
  else do;
    days=job_card_dt - lag(job_card_dt);
    kms=kms-lag(kms);
  end;
run;
Trusted Advisor
Posts: 1,019

Re: How to calculate difference in days from two observations within a BY group?

It can be even simpler with use of the IFN and DIF (instead of lag) functions:

 

data want;
   set have;
   by id;
   days=ifn(first.id,0,dif(job_card_dt));
run;
Occasional Contributor
Posts: 5

Re: How to calculate difference in days from two observations within a BY group?

Thanks mkeintz! That was short and crisp!
Solution
‎11-10-2016 07:13 AM
Super User
Posts: 5,504

Re: How to calculate difference in days from two observations within a BY group?

The more the merrier?

 

data want;

set have;

by ID;

Kms = dif(KM_reading);

days = dif(Job_Card_Dt);

if first.id then do;

   kms=0;

   days=0;

end;

run;

 

When using DIF or LAG, it's important (for at least 99% of applications) to make sure they execute on each observation.  I believe that IFN handles this properly, but would test it to make sure if that is your tool of choice.

Super User
Posts: 7,782

Re: How to calculate difference in days from two observations within a BY group?

Posted in reply to Astounding

Just tested it, because I had the same objection (conditional execution of dif())

IFN will always execute the function, regardless of the outcome of the condition.

If one had formulated the same logic with the common if-then-else, it would fail.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,019

Re: How to calculate difference in days from two observations within a BY group?

Posted in reply to KurtBremser

IFN and IFC calcuate both alternative results (thereby updating the lag queue), even though they only return one of the alternatives.  This is a long-published property of IFN and IFC  (I remember reading a paper on the subject many years ago by Howard Schrier ("Conditional Lags Don't Have to be Treacherous"  http://www.howles.com/saspapers/CC33.pdf).  I used this in a paper ("Leads and Lags: Static and Dynamics Queues in theh SAS Data Step") for lags in by groups, as well as a somewhat more exotic example in getting leads.

 

regards,

Mark

Super User
Posts: 7,782

Re: How to calculate difference in days from two observations within a BY group?

Always something to be learned. I love this forum.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 313 views
  • 7 likes
  • 5 in conversation