Retaining values across by groups

[buechler66]

Hi all. I've got a tough one here.  I have the following 3 columns of data.  Here's what I need to accomplish...

Whenever a person (PERS_ID) is assigned to a new provider (PROV_ID) I need to calculate how many days have pasted from the previous appointment date (APPT_DATE) for the previous provider to the current appointment date of the new provider.

For example, at record 2 the PROV_ID changes for PERS_ID 134841 from 1386065225 to 1235209446. So I need to calculate the days difference between 17-Aug-17 and 1-Aug-17.

For example, at record 6 the PROV_ID changes for PERS_ID 134841 from 1235209446 to 1144621806. So I need to calculate the days difference between 16-Oct-17 and 16-Nov-17.

I need help accomplishing this if you know a solution?  Thanks so much.

PERS_ID PROV_ID APPT_DATE Calc_days
134841 1386065225 1-Aug-17
134841 1235209446 17-Aug-17 16
134841 1235209446 17-Aug-17
134841 1235209446 16-Oct-17
134841 1235209446 16-Oct-17
134841 1144621806 16-Nov-17 31
134841 1144621806 16-Nov-17
134841 1144621806 15-Jan-18
134841 1144621806 15-Jan-18
134841 1235209446 30-May-18 ?
134841 1235209446 30-May-18
134841 1235209446 29-Jul-18
134841 1235209446 29-Jul-18
927789 1992766273 13-Aug-17
927789 1992766273 13-Aug-17
927789 1992766273 12-Oct-17
927789 1992766273 12-Oct-17
927789 1871889956 10-Nov-17 ?
927789 1871889956 10-Nov-17
927789 1447417639 29-Nov-17 ?
927789 1306858121 18-Jan-18 ?
927789 1306858121 19-Mar-18
927789 1306858121 18-May-18
1647935 1679803183 14-Sep-17
1647935 1679803183 14-Sep-17
1647935 1477695468 16-Mar-18 ?
1647935 1477695468 16-Mar-18
1647935 1295738037 21-Jun-18 ?
1647935 1114984275 27-Jul-18 ?
1647935 1114984275 27-Jul-18

[novinosrin · Accepted solution]

do you mean something like this?

data have;
input PERSON_ID PROVIDER_ID APPT_DATE :date9.;
format APPT_DATE date7.;
cards;
134841 1386065225 1-Aug-17
134841 1235209446 17-Aug-17
134841 1235209446 17-Aug-17
134841 1235209446 16-Oct-17
134841 1235209446 16-Oct-17
134841 1144621806 16-Nov-17
134841 1144621806 16-Nov-17
134841 1144621806 15-Jan-18
134841 1144621806 15-Jan-18
134841 1235209446 30-May-18
134841 1235209446 30-May-18
134841 1235209446 29-Jul-18
134841 1235209446 29-Jul-18
927789 1992766273 13-Aug-17
927789 1992766273 13-Aug-17
927789 1992766273 12-Oct-17
927789 1992766273 12-Oct-17
927789 1871889956 10-Nov-17
927789 1871889956 10-Nov-17
927789 1447417639 29-Nov-17
927789 1306858121 18-Jan-18
927789 1306858121 19-Mar-18
927789 1306858121 18-May-18
1647935 1679803183 14-Sep-17
1647935 1679803183 14-Sep-17
1647935 1477695468 16-Mar-18
1647935 1477695468 16-Mar-18
1647935 1295738037 21-Jun-18
1647935 1114984275 27-Jul-18
1647935 1114984275 27-Jul-18
;

data want;
set have;
by person_id;
want=ifn( not first.person_id and lag(PROVIDER_ID) ne PROVIDER_ID , dif(APPT_DATE),.);
run;

[buechler66]

Wow! How in the world did you do that so fast?! And so simply with just a few lines of code. Thank you so so much! I really appreciate it.

[SuryaKiran]

Please provide your sample data in a proper format, by providing in a data step you can expect more people responding quickly. 

[buechler66]

oh no. I thought I had. I guess I don't know the proper format. Any suggestions?

[novinosrin]

Hi @buechler66  did you not notice my solution? Please let me know whether that works or not

[buechler66]

I really struggled to get my data in a usable format but it posted in an unfriendly way.  Is there a best way to post sample data when asking for help?

[novinosrin]

Hi @buechler66   Don't worry about it. All I need is something i can copy paste, which you did right. I don't care about the datastep form. It's less than 25 seconds work. Chill