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
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;
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;
Please provide your sample data in a proper format, by providing in a data step you can expect more people responding quickly.
Hi @buechler66 did you not notice my solution? Please let me know whether that works or not
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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.