DATA Step, Macro, Functions and more

How to perform a subtraction calculation by retaining the previous data entry?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

How to perform a subtraction calculation by retaining the previous data entry?

What im trying to do is determine the days between dates for a particular customer.

This is ideally what i would like the output to look like.

Customer_id     contact_date     Days

123                    01-01-2014       0

123                    04-01-2014       3    

123                    09-01-2014       5

123                    11-01-2014       2

124                    01-01-2014       0

124                    11-01-2014     10 

I have tried sorting by customer_id and then performing a first.customer_id and then retaining the contact_date however it doesnt seem to work.

Data days;

set contact_date;

by customer_id;

retain first_day;

if first.customer_id then do

first_day = contact_date;

end;

else

  do;

  if nmiss(contact_date,first_day)= 0 then days_diff = contact_date - first_day;

  else first_day = targetted_date;

  end;

run;

This results in the difference being from the first_date which i dont want, so its not quite what i want, i want the subtraction to be from the previous record not the first record.

Any assistance would be greatly appreciated!


Accepted Solutions
Solution
‎06-11-2014 03:50 AM
Super Contributor
Posts: 297

Re: How to perform a subtraction calculation by retaining the previous data entry?

How about this?

DATA WANT;

  SET HAVE;

  BY CUSTOMER_ID;

  FORMAT PREV_DAY DATE9.;

  PREV_DAY = LAG(CONTACT_DATE);

  IF FIRST.CUSTOMER_ID THEN DAYS = 0;

  ELSE DAYS = DATDIF(PREV_DAY,CONTACT_DATE,"ACT/ACT");

RUN;

We use the lag function to retain the value from the previous observation.  You can drop the PREV_DATE variable.  I just left it in so you can see what is happening.

View solution in original post


All Replies
Solution
‎06-11-2014 03:50 AM
Super Contributor
Posts: 297

Re: How to perform a subtraction calculation by retaining the previous data entry?

How about this?

DATA WANT;

  SET HAVE;

  BY CUSTOMER_ID;

  FORMAT PREV_DAY DATE9.;

  PREV_DAY = LAG(CONTACT_DATE);

  IF FIRST.CUSTOMER_ID THEN DAYS = 0;

  ELSE DAYS = DATDIF(PREV_DAY,CONTACT_DATE,"ACT/ACT");

RUN;

We use the lag function to retain the value from the previous observation.  You can drop the PREV_DATE variable.  I just left it in so you can see what is happening.

Occasional Contributor
Posts: 16

Re: How to perform a subtraction calculation by retaining the previous data entry?

Posted in reply to Scott_Mitchell

Thanks a lot for the prompt response, it works correctly, much appreciated!

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 214 views
  • 0 likes
  • 2 in conversation