Fluorite | Level 6

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

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!

8 REPLIES 8
Super User

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

``````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;``````
Fluorite | Level 6

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

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?

PROC Star

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

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;
``````
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Fluorite | Level 6

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

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!

PROC Star

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

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Fluorite | Level 6

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

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!

PROC Star

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

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Super User

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

``````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;``````
Discussion stats
• 8 replies
• 1533 views
• 0 likes
• 3 in conversation