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

Solved
Occasional Contributor
Posts: 16

# 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: 312

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

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.

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

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

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