topic Re: how to compile contious visit dates in SAS Programming

how to compile contious visit dates

sas_student1 — Tue, 03 Apr 2018 16:49:00 GMT

Hello SAS community,

I have a question on how to consolidate dates to start and end date by client where the dates are continuous

What I have is data on each client and each day they had a visit. (see SAS code below).

What I want is that if there has not been a break in a visit, so a client came in everyday, then to have a start date and end date. if there was a break in a visit then to have a new row that has a new start date and end date (see want table below).

Suggestions on how to accomplish this? I am sure the code is really simple but cannot seem to figure it out

Thank you

data have;
informat client 1. date mmddyy10.;
input client date;
format date mmddyy10.;
datalines;
1	10122017
1	10132017
1	10142017
1	10152017
1	10162017
1	10172017
1	10182017
1	10192017
1	10212017
1	10222017
1	10232017
1	10242017
1	10252017
1	10262017
1	10272017
2	01172017
2	01182017
2	01192017
2	01202017
2	01232017
2	01242017
2	01252017
2	01262017
2	01272017
3	05042017
3	05052017
3	05062017
3	05072017
3	05082017
3	05092017
3	05102017
3	05112017
;
run;

What I need is:

Client	Start_date	end_date
1	10/12/2017	10/19/2017
1	10/21/2017	10/27/2017
2	1/17/2017	1/20/2017
2	1/23/2017	1/27/2017
3	5/4/2017	5/11/2017

Re: how to compile contious visit dates

Reeza — Tue, 03 Apr 2018 16:55:22 GMT

Try the DIF function.

If DIF() > 1 then that's the end and you need to start a new record/entry.

@sas_student1 wrote:

Hello SAS community,

I have a question on how to consolidate dates to start and end date by client where the dates are continuous

What I have is data on each client and each day they had a visit. (see SAS code below).

What I want is that if there has not been a break in a visit, so a client came in everyday, then to have a start date and end date. if there was a break in a visit then to have a new row that has a new start date and end date (see want table below).

Suggestions on how to accomplish this? I am sure the code is really simple but cannot seem to figure it out

Thank you
data have;
informat client 1. date mmddyy10.;
input client date;
format date mmddyy10.;
datalines;
1	10122017
1	10132017
1	10142017
1	10152017
1	10162017
1	10172017
1	10182017
1	10192017
1	10212017
1	10222017
1	10232017
1	10242017
1	10252017
1	10262017
1	10272017
2	01172017
2	01182017
2	01192017
2	01202017
2	01232017
2	01242017
2	01252017
2	01262017
2	01272017
3	05042017
3	05052017
3	05062017
3	05072017
3	05082017
3	05092017
3	05102017
3	05112017
;
run;
What I need is:

Client Start_date end_date

1 10/12/2017 10/19/2017

1 10/21/2017 10/27/2017

2 1/17/2017 1/20/2017

2 1/23/2017 1/27/2017

3 5/4/2017 5/11/2017

Re: how to compile contious visit dates

mkeintz — Tue, 03 Apr 2018 17:14:50 GMT

Determining whether the record in hand is the start of a new time-span is relatively easy. But verifying that the record-in-hand is the END of a time-span requires looking ahead at the next record. This program achieves that task via a data step with a self-merge and a "firstobs=2" parameter:

data want (keep=client start_date end_date);
  merge have 
        have (firstobs=2 keep=client date rename=(client=nxt_client date=nxt_date));

  retain start_date;

  if client^=nxt_client or nxt_date>date+1 then do;
    end_date=date;
    output;
    start_date=nxt_date;
  end;
  format start_date end_date mmddyy10.;
run;

Re: how to compile contious visit dates

novinosrin — Tue, 03 Apr 2018 18:15:01 GMT

data have;
informat client 1. date mmddyy10.;
input client date;
format date mmddyy10.;
datalines;
1	10122017
1	10132017
1	10142017
1	10152017
1	10162017
1	10172017
1	10182017
1	10192017
1	10212017
1	10222017
1	10232017
1	10242017
1	10252017
1	10262017
1	10272017
2	01172017
2	01182017
2	01192017
2	01202017
2	01232017
2	01242017
2	01252017
2	01262017
2	01272017
3	05042017
3	05052017
3	05062017
3	05072017
3	05082017
3	05092017
3	05102017
3	05112017
;
run;

data want;

set have;
by client;

k=dif(date);
retain startdate enddate;

if first.client then startdate=date;
else if k=1 then enddate=date;

if  not first.client and k>1 or last.client and k=1  then do;

    output;startdate=date;
    call missing(enddate);

end;

format startdate enddate mmddyy10.;
keep client startdate enddate;

run;

Re: how to compile contious visit dates

Reeza — Tue, 03 Apr 2018 18:15:51 GMT

@novinosrin Note that your code got a bit garbled so I cleaned it up to show properly. This seems to happen if you edit code in the main window rather than the specific pop up.

Re: how to compile contious visit dates

sas_student1 — Tue, 03 Apr 2018 18:17:14 GMT

thank you @mkeintz this almost worked!

I am missing the first start date for the first client .

so the table looks like:

client	start_date	end_date
1	.	10/19/2017
1	10/21/2017	10/27/2017
2	1/17/2017	1/20/2017
2	1/23/2017	1/27/2017
3	5/4/2017	5/11/2017

how do I get the 10/12/17 in the . ?

Re: how to compile contious visit dates

mkeintz — Tue, 03 Apr 2018 18:27:07 GMT

@sas_student1: You have observed that only one case of START_DATE is not as desired, then first time span.

As a student, this is a good opportunity to implement some thing you probably know, or at least something worthwhile for every student to learn. Namely:

How to issue a statement that effectively says "if this is the first iteration of the data step then initialize the START_DATE value to an appropriate value"

That is, a statement such as

if (some condition) then start_date = (some value);

So, what's the condition, and what's the value?

Re: how to compile contious visit dates

sas_student1 — Tue, 03 Apr 2018 18:40:49 GMT

@mkeintz thank you!

okay so to your question "whats the condition and whats the value" I would think it would be :

if first.client and first.date then start_date= date?

But that is not exactly right, is it? I tried it and it didn't work.

the dif(date) by @novinosrin and the fix by @Reeza did work, but trying to figure out (how/why) and how do I alter @mkeintz code to get the first clients date not missing.

Thank you

Re: how to compile contious visit dates

novinosrin — Tue, 03 Apr 2018 18:44:49 GMT

Hi @Reeza just a quick question please. I tend to write the code in the PC SAS at my college lab and paste here using the running man icon as it pops a small window. I was told the same as what you said by an OP in another thread. Is something wrong at my end or am i doing not right?

Sorry for the trivial stuff, I just wanna get this right. Thank you!

Re: how to compile contious visit dates

sas_student1 — Tue, 03 Apr 2018 18:49:27 GMT

thanks @novinosrin question: what does the

call missing (enddate) do?

Thanks

Re: how to compile contious visit dates

SDally — Tue, 03 Apr 2018 18:52:04 GMT

data have2;
   set have;
   by client;
   lagdate = lag(date);
   if first.client then episode=1;
     else if date-lagdate>1 then episode+1;
run;

proc summary data=have2;
  by client episode;
  var date;
  output out=want(drop=_: episode) min=start_date max=stop_date;
run;

Another approach.

Re: how to compile contious visit dates

novinosrin — Tue, 03 Apr 2018 18:52:06 GMT

assigns missing values exactly as documented. I googled it too.

Re: how to compile contious visit dates

sas_student1 — Tue, 03 Apr 2018 19:13:14 GMT

@SDally yes this worked too!

Thank you!

Re: how to compile contious visit dates

Astounding — Tue, 03 Apr 2018 19:30:34 GMT

As a rule, I find it easier to handle this type of problem in two steps instead of one. First, find the groupings:

data temp;

set have;

by client;

if first.client or dif(date) ne 1 then group + 1;

run;

Then find the endpoints:

proc summary data=temp;

by client group;

var date;

output out=want (keep=client start_date end_date) min=start_date max=end_date;

run;

************ EDITED:

sorry, didn't notice a very similar solution. However, notice that this condition is not sufficient:

date-lagdate>1

If one client started earlier than the previous client, the value for that expression could be negative.

Re: how to compile contious visit dates

ballardw — Tue, 03 Apr 2018 20:31:30 GMT

@novinosrin wrote:

Hi @Reeza just a quick question please. I tend to write the code in the PC SAS at my college lab and paste here using the running man icon as it pops a small window. I was told the same as what you said by an OP in another thread. Is something wrong at my end or am i doing not right?

Sorry for the trivial stuff, I just wanna get this right. Thank you!

There seems to be some intermittent behavior with things pasted into the "running man" and I have no idea what causes it. Sometimes the result will show as a "spoiler" with a right arrow instead of inline displayed text, sometimes text gets scrambled in other ways, possibly the result of characters that you do not see if you copy code from online (message windows here for example), edit and paste back.

I tend to use the {I} as it seems more of a "pure text" format, no color highlighting for example. But even there I've seen some odd things happen.

Just check the appearance of your post after it posts and edit if needed by clicking on the wheel to the upper right of the posted message and selecting "edit".

Re: how to compile contious visit dates

novinosrin — Tue, 03 Apr 2018 20:43:03 GMT

@ballardw Thank you Sir. Hmm sounds like a remedy ticket needs to be raised with community 🙂 I will be more aware. Thank you for your time. Have a nice evening!

Re: how to compile contious visit dates

mkeintz — Tue, 03 Apr 2018 21:13:23 GMT

The "then start_date=date" is correct.

The "if first.client and first.date" condition would be wrong, because:

There is no BY statement to even generate the first.client and first.date dummy variables
Even if there were, the condition ("first.client and first.date") would select EVERY observation from a daily file.
1. But a "first.client" alone would work, although it's overkill.

What I meant was

"if _N_=1 then start_date=date;"

placed in front of the do group. The automatic variable _N_ counts the current iteration number in the DATA step. In this sample _N_ is tracking the number of MERGE actions (once for each incoming observation). So _N_=1 is the first observation.

. It selects the first record for a givne client/date - which means every record in your daily file.

Now "if first.client" would work, but is a sligh