topic Re: Retaining values across by groups in SAS Programming

Retaining values across by groups

buechler66 — Mon, 04 Feb 2019 22:06:20 GMT

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

Re: Retaining values across by groups

novinosrin — Mon, 04 Feb 2019 22:05:53 GMT

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;

Re: Retaining values across by groups

SuryaKiran — Mon, 04 Feb 2019 22:11:34 GMT

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

Re: Retaining values across by groups

buechler66 — Mon, 04 Feb 2019 22:14:55 GMT

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

Re: Retaining values across by groups

novinosrin — Mon, 04 Feb 2019 22:17:26 GMT

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

Re: Retaining values across by groups

buechler66 — Mon, 04 Feb 2019 22:20:43 GMT

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.

Re: Retaining values across by groups

buechler66 — Mon, 04 Feb 2019 22:23:12 GMT

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?

Re: Retaining values across by groups

novinosrin — Mon, 04 Feb 2019 22:27:29 GMT

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