How do I calculate sum for each interval (i.e., month)?

[jsyang56]

Hello,

I would like someone to help me out with the following problem. My current table looks like this, 

enrolid	indexdate	lastdate	service_date	pay
1	08/03/2019	12/31/2019	09/05/2019	$10
1	08/03/2019	12/31/2019	11/06/2019	$10
2	01/01/2019	06/30/2019	03/30/2019	$5
2	01/01/2019	06/30/2019	06/23/2019	$5
2	01/01/2019	06/30/2019	06/25/2019	$7
3*	01/01/2019	12/31/2019	-	-

Key: 

indexdate = follow-up start date

last_date = follow-up last date

service_date = date of each claim

pay = total cost for each claim 

The table I want is this, 

Enrolid	month1	month2	month3	month4	month5	month6	month7	month8	month9	month10	month11	month12
1	$0	$10	$0	$10	$0	.	.	.	.	.	.	.
2	$0	$0	$5	$0	$0	$12	.	.	.	.	.	.
3	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0

I would like to calculate the cost for each month of follow-up.

• If there was no claim for each month of follow-up, then it would be $0
• For the months after the follow-up duration, then the cost would be null
  • ex) For patient 1, there are only 5 months of follow-up duration, so from month6 and after the cost would be null or "." ;
  • *Compared to patient 3, even though he didn't have any claims all months during the follow-up duration would be $0.
• I would like to extend the follow-up duration to 48 months.

I'm not sure what happened but my earlier posts got deleted, so I'm reposting them. Please let me know if you need any clarification.

Thanks! 

[Ksharp]

data have;
infile cards expandtabs truncover;
input enrolid	(indexdate	lastdate service_date) (: mmddyy12.)		pay : dollar32.;
format indexdate	lastdate service_date mmddyy10. pay dollar32.;
cards;
1	08/03/2019	12/31/2019	09/05/2019	$10
1	08/03/2019	12/31/2019	11/06/2019	$10
2	01/01/2019	06/30/2019	03/30/2019	$5
2	01/01/2019	06/30/2019	06/23/2019	$5
2	01/01/2019	06/30/2019	06/25/2019	$7
3	01/01/2019	12/31/2019	.	.
;

data temp;
 set have;
 month=1;
 if not missing(indexdate) and not missing(service_date) then
  month=ceil(yrdif(indexdate,service_date,'age')*12);
run;
proc summary data=temp nway;
class enrolid month;
var pay;
output out=temp2(drop=_type_ _freq_) sum=;
run; 
data month;
do month=1 to 48;
output;
end;
run;
proc sql;
create table temp3 as
select a.*,coalesce(pay,0) as pay format=dollar32.
 from (select * from (select distinct enrolid from temp2),(select * from month)) as a 
  natural left join temp2;
quit;
proc transpose data=temp3 out=want(drop=_:) prefix=month_;
by enrolid;
var pay;
run;

[jsyang56]

Hello,

Thank you for your input! With your method I'm like 90% done, the only part I'm missing is that I would like months after their follow-up duration (months between the last_date and indexdate) to be null. 

• For example, patient 2's costs after month 6 should be null

However, the current data set has all months without any values to be $0, instead of null. How would you incorporate the last_date into your code? 

[mkeintz]

I think this is a good case for a 48-element array:

data have;
infile cards expandtabs truncover;
input enrolid	(indexdate	lastdate service_date) (: mmddyy12.)		pay : dollar32.;
format indexdate	lastdate service_date mmddyy10. pay dollar32.;
cards;
1	08/03/2019	12/31/2019	09/05/2019	$10
1	08/03/2019	12/31/2019	11/06/2019	$10
2	01/01/2019	06/30/2019	03/30/2019	$5
2	01/01/2019	06/30/2019	06/23/2019	$5
2	01/01/2019	06/30/2019	06/25/2019	$7
3	01/01/2019	12/31/2019	.       	.
;
data want (drop=m indexdate lastdate service_date pay);
  do until (last.enrolid);
    set have;
    by enrolid;
    array month{48};
    if service_date^=. then month{1+intck('month',indexdate,service_date)}=pay;
  end;
  
  do m=1 to intck('month',indexdate,lastdate)+1;
    if month{m}=. then month{m}=0;
  end;
run;

[jsyang56]

Hello,

Thank you for your input! With your solution, I'm 90% close to being done. Currently, each month only shows one pay, instead of the sum of multiple 'pay' rows. How would you modify it to include multiple rows of pay for each month? 

For example, from the table that looks like this,

Enrolid	indexdate	lastdate	service date	pay
2	01/01/2019	06/30/2019	06/23/2019	$5
2	01/01/2019	06/30/2019	06/25/2019	$7
2	01/01/2019	06/30/2019	06/25/2019	$12

Your code makes it look like this

enrolid	month1	month2	month3	month4	month5	month6	month7	month8	month9	month10	month11	month12
2	$0	$0	$0	$0	$0	$12	.	.	.	.	.	.

• Only showing the last pay row for each month, instead of sum of pay
  • So, month6 should be $24 instead of $12.

Other than that this is exactly what I wanted the table to look like! 

[mkeintz]

In the program I submitted, you see the statement:

    if service_date^=. then month{1+intck('month',indexdate,service_date)}=pay;

which would overwrite any earlier payment for a given month with the latter payment for the same month.   So instead of assigning an incoming pay value (i.e. "month{...}=pay") as is done above, what do you think should be done with the incoming value of pay?   In particular take a look at the  SAS.Sum function 

Also, we can be much more effective and efficient in helping if the problem is fully specified at the start.  Otherwise, mission creep can often make initial responses totally useless, although that's not the case here.

[jsyang56]

Hi,

First of all, I really appreciate the time and effort you put into this problem and helping me out. I believe the original problem includes the sum of pay (e.g., patient 2, month 6), but maybe I should have been more clear with my question. 

I tried different ways to incorporate the 'sum' function to the code, but nothing seems to be working. Can you provide me with some hints, please?

So far I have tried,

 if service_date^=. then month{1+intck('month',indexdate,service_date)}= sum(pay);

 if service_date^=. then month{1+intck('month',indexdate,service_date)}= sum(of pay);

This is probably super basic, but I'm very new to this so please bear with me! 

[mkeintz]

You want to sum the incoming PAY value with any value already present for the corresponding month.

I.e., you want something like

month{m}=sum(month{m},pay);

where m above is a stand in for 1 plus the number of months between indexdate and service_date.

[Ksharp]

data have;
infile cards expandtabs truncover;
input enrolid	(indexdate	lastdate service_date) (: mmddyy12.)		pay : dollar32.;
format indexdate	lastdate service_date mmddyy10. pay dollar32.;
cards;
1	08/03/2019	12/31/2019	09/05/2019	$10
1	08/03/2019	12/31/2019	11/06/2019	$10
2	01/01/2019	06/30/2019	03/30/2019	$5
2	01/01/2019	06/30/2019	06/23/2019	$5
2	01/01/2019	06/30/2019	06/25/2019	$7
3	01/01/2019	12/31/2019	.	.
;

data temp;
 set have;
 month=1;
 if not missing(indexdate) and not missing(service_date) then
  month=ceil(yrdif(indexdate,service_date,'age')*12);
run;
proc summary data=temp nway;
class enrolid month;
var pay;
output out=temp2(drop=_type_ _freq_) sum=;
run; 

proc summary data=temp2 nway;
class enrolid ;
var month pay;
output out=temp22(drop=_type_ _freq_) max= ;
run; 
data temp23;
 set temp22;
 _pay=0;
 if not missing(pay) then do;
 do month=1 to month;
  output;
 end;
 end;
 else do;
 do month=1 to 48;
  output;
 end;
 end;
drop pay;
run;


data month;
do month=1 to 48;
output;
end;
run;
proc sql;
create table temp3 as
select a.*, coalesce(pay,_pay) as pay format=dollar32.
 from (select * from (select distinct enrolid from temp2),(select * from month)) as a 
  natural left join temp23
  natural left join temp2;
quit;
proc transpose data=temp3 out=want(drop=_:) prefix=month_;
by enrolid;
var pay;
run;