BookmarkSubscribeRSS Feed
jsyang56
Fluorite | Level 6

Hello,

 

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

 

enrolidindexdatelastdateservice_datepay
108/03/201912/31/201909/05/2019$10
108/03/201912/31/201911/06/2019$10
201/01/201906/30/201903/30/2019$5
201/01/201906/30/201906/23/2019$5
201/01/201906/30/201906/25/2019$7
3*01/01/201912/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, 

Enrolidmonth1month2month3month4month5month6month7month8month9month10month11month12
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
Ksharp
Super User
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
Fluorite | Level 6

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
PROC Star

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

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

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,

Enrolidindexdatelastdateservice datepay
201/01/201906/30/201906/23/2019$5
201/01/201906/30/201906/25/2019$7
201/01/201906/30/201906/25/2019$12

 

Your code makes it look like this

enrolidmonth1month2month3month4month5month6month7month8month9month10month11month12
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
PROC Star

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

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

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
PROC Star

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

--------------------------
Ksharp
Super User
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1533 views
  • 0 likes
  • 3 in conversation