## Solving for days between records

Solved
Occasional Contributor
Posts: 5

# Solving for days between records

I'm a newer user to the SAS world so there very well may be an easy way to do this, but at the moment i'm stuck. I need to calculate the number of days between my records. Currently I've got some code worked out that will solve for the Days to Next Contact (DTNC) and Days Since Last Contact (DSLC). That code is below.

What I need is code that will solve for additional contacts in either direction. Stated in another way, given contacts 1 through 5, I need to be able to look a the third contact and say "it was W days earlier that contact 1 happened, X days earlier that contact two happened, Y days later that contact 4 happened ,and Z days later that contact 5 happened." My metrics DSLC and DTNC take care of contacts 2 and 4, but i'm lost trying to solve for 1 and 5.

Any help would be GREATLY appreciated.

Thanks,

-Mike

The date I need to reference is "CALLDATE". I create the variable DATE for the purpose of calculation.

proc sort                    /*CallCount*/

data= work.callcount; by ANI CallDate; run;

data DSLC;                /*from WEEKS - creates DSLC*/

set work.callcount; retain date; by ANI;

if first.ani then do;

DSLC = .;

date=CallDate;

end;

DSLC=CallDate-date;

date=CallDate;

run;

data DSLC2;            /*clears DSLC if only 1 call on record*/

set work.DSLC; by ANI;

if count=1 then DSLC=.;

run;

data DTNC1;           /*Creates counter for DTNC2*/

set work.DSLC2; retain order; by ANI;

if first.ani then do;

order = count+1;

end;

order=order-1;

run;

proc sort                 /*DTNC*/

data=work.DTNC1;  by ani order;

run;

data DTNC2;            /*Creates DTNC - From DTNC1*/

set work.DTNC1; retain day; by ANI;

if first.ani then do;

DTNC = .;

day=CallDate;

end;

DTNC=day-CallDate;

day=CallDate;

run;

proc sql;                /*creates DTNC3 - From DTNC2*/

create table DTNC3 as

Select ANI,

CustNumber,

Guide,

AcctType,

CallDate,

CallTime,

COUNT,

DSLC,

DTNC

From work.DTNC2;

quit;

data MASTER;           /*Creates MASTER - From DTNC3 - Clears DTNC if only 1 call on record*/

set work.DTNC3; by ANI;

if count=1 then DTNC=.;

run;

Accepted Solutions
Solution
‎01-28-2014 10:28 AM
Super User
Posts: 6,773

## Re: Solving for days between records

Mike,

It's not that the programming is so difficult, but it's probably vital to explore the problem a bit.  Do you know the maximum number of contacts per ANI?  If there are 10 contacts maximum,  you are asking for 18 variables to be added to every single observation in the data set.  After all, there could be as many as 9 earlier contacts, and as many as 9 later contacts.  Would that be the desired result?  Should two contacts on the same date be lumped together or could the "days since last contact" be zero?  As an intermediate step, the programming could involve transposing the dates per ANI, and getting (for example) contact_date_1 through contact_date_10 added to every observation.  Would that much be a sufficient, or perhaps even a better, solution?

Good luck.

All Replies
Super User
Posts: 23,721

## Re: Solving for days between records

What does your data look like and what do you want?

For problems like this it's easiest if you post a sample data set and sample output that corresponds to said dataset (HAVE and WANT)

Solution
‎01-28-2014 10:28 AM
Super User
Posts: 6,773

## Re: Solving for days between records

Mike,

It's not that the programming is so difficult, but it's probably vital to explore the problem a bit.  Do you know the maximum number of contacts per ANI?  If there are 10 contacts maximum,  you are asking for 18 variables to be added to every single observation in the data set.  After all, there could be as many as 9 earlier contacts, and as many as 9 later contacts.  Would that be the desired result?  Should two contacts on the same date be lumped together or could the "days since last contact" be zero?  As an intermediate step, the programming could involve transposing the dates per ANI, and getting (for example) contact_date_1 through contact_date_10 added to every observation.  Would that much be a sufficient, or perhaps even a better, solution?

Good luck.

Occasional Contributor
Posts: 5

## Re: Solving for days between records

That's a fantastic idea. It hadn't occurred to me. This is what i've got . . . We will most likely add 30 new variables, but ultimately this is what i'm looking for. Thanks!

data DSLC;

set work.callcount;

retain date date2 date3 date4 date5 date6 DSLC;

by ANI CallDate;

if first.ani then do;

DSLC = .;

DSLC2 = .;

DSLC3 = .;

date=CallDate;

date2=CallDate;

date3=Calldate;

date4=CallDate;

date5=calldate;

date6=calldate;

end;

DSLC=CallDate-date;

DSLC2=DATE-DATE2;

DSLC3=DATE-DATE3;

DSLC4=DATE-DATE4;

DSLC5=DATE-DATE5;

DSLC6=DATE-DATE6;

date=CallDate;

date2=calldate-DSLC;

date3=calldate-DSLC2;

date4=CALLDATE-DSLC3;

date5=CALLDATE-DSLC4;

DATE6=CALLDATE-DSLC5;

run;

Super User
Posts: 23,721

Look up arrays

Super User
Posts: 6,773

## Re: Solving for days between records

I'm not sure which parts of this you would feel comfortable doing ... are you able to transpose the data to get the contacted dates onto a single observation?  Then merge that back onto the original data BY ANI?

It would be helpful to add a sequential call number at that point:

data want;

merge have transposed_dates;

by ANI;

if first.ANI then call_number=1;

else call_number + 1;

run;

Then the added computations could utilize all the information ahead of time, or you could wait to perform the computations until you absolutely need the information.  For example, the "add computations" section could include:

array cdates {*} contact_date_:;

if call_number < dim(cdates) then DUNC1 = cdates{call_number + 1} - cdates{call_number};

if call_number > 3 then DSLC3 = cdates{call_number} - cdates{call_number - 3};

Like Reeza said, you would need to familiarize yourself with arrays to perform these computations.  And the reference to contact_date_: means that the variables in the array would be all variable names that begin with the characters "contact_date_".

🔒 This topic is solved and locked.