BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MikeR_Indy_
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

5 REPLIES 5
Reeza
Super User

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)

Astounding
PROC Star

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.

MikeR_Indy_
Calcite | Level 5

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;

Reeza
Super User

Look up arrays Smiley Happy

Astounding
PROC Star

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;

   *** add computations if desired;

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_".

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 917 views
  • 0 likes
  • 3 in conversation