topic Re: How to perform a subtraction calculation by retaining the previous data entry? in SAS Programming

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

rjnc13 — Wed, 11 Jun 2014 07:37:30 GMT

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!

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

Scott_Mitchell — Wed, 11 Jun 2014 07:50:43 GMT

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.

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

rjnc13 — Wed, 11 Jun 2014 08:05:19 GMT

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