SAS9.4 How to sum values over a range of dates?

[sas112]

PatientID    SvcDate        OpCode     Pay
----------       --------            -------          ----
101             01/13/2013    19              30.7
101             01/14/2013    12              11.3
101             01/16/2013    13              28.0
101             01/16/2013    12              60.2
101             02/09/2013    10              11.4
102             02/10/2013    12              9.2
102             02/10/2013    19              13.2
102             02/11/2013    13              32.1
102             02/12/2013    14              17.5
102             02/13/2013    15              10.2
102             02/19/2013    12              14.3

Hello, I am trying to sum up the **Pay** variable for the observations that are 2 days before and 2 days after the observation with **Opcode = 13**. For example, for **PatientID = 101**, the **Opcode = 13** observation falls on **SVCDate = 01/16/2013**, so I would like to sum up all the **Pay** variables for **SVCDate = 01/14/2013 to SVCDate = 01/18/2013**. So this would include the observations for Patient 101 with **Pay** variable equal to 11.3, 28.0, and 60.2. Thus, the operation would be 11.3 + 28.0 + 60.2 = 99.5. I would like to put this 99.5 into a new table that has 2 variables: PatientID and DirectProcedureCost. DirectProcedureCost for Patient 101 would be 99.5.

I would like to loop through all my patient IDs and be able to obtain the DirectProcedureCost for each one. What would be the best way in SAS9.4 to do this?

Thank you in advance for the help!

[PeterClemmensen]

Can optcode be equal to 13 in more than one obs for each PatiendtID?

[Kurt_Bremser · Accepted solution]

With a SQL sub-select:

data have;
input PatientID $ SvcDate :mmddyy10. OpCode :$2. Pay;
format svcdate yymmdd10.;
datalines;
101             01/13/2013    19              30.7
101             01/14/2013    12              11.3
101             01/16/2013    13              28.0
101             01/16/2013    12              60.2
101             02/09/2013    10              11.4
102             02/10/2013    12              9.2
102             02/10/2013    19              13.2
102             02/11/2013    13              32.1
102             02/12/2013    14              17.5
102             02/13/2013    15              10.2
102             02/19/2013    12              14.3
;

proc sql;
create table want as
  select a.patientid,
  (
    select sum(pay)
    from have b
    where
      abs(a.svcdate - b.svcdate) le 2 and
      a.patientid = b.patientid
  ) as DirectProcedureCost
  from have a
  where a.opcode = '13'
;
quit;

Note how source data is presented as a data step with datalines; please do so yourself in the future, so we can recreate an exact copy of the dataset with copy/paste and submit.

[PeterClemmensen]

Here is my approach. 

Let me know if this works for you and feel free to ask.

data have;
input PatientID SvcDate :mmddyy10. OpCode Pay;
format SvcDate mmddyy10.;
datalines;
101 01/13/2013 19 30.7
101 01/14/2013 12 11.3
101 01/16/2013 13 28.0
101 01/16/2013 12 60.2
101 02/09/2013 10 11.4
102 02/10/2013 12 9.2
102 02/10/2013 19 13.2
102 02/11/2013 13 32.1
102 02/12/2013 14 17.5
102 02/13/2013 15 10.2
102 02/19/2013 12 14.3
;

data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : "have", multidata : "Y");
      h.definekey("PatientID", "SvcDate");
      h.definedata("Pay");
      h.definedone();
   end;

   set have;
   where OpCode = 13;

   DirectProcedureCost = 0;

   do dt = SvcDate - 2 to SvcDate + 2;
      do while(h.do_over(key : PatientID, key : dt) = 0);
         DirectProcedureCost + Pay;
      end;
   end;

   keep PatientID DirectProcedureCost;
run;

Result:

PatientID DirectProcedureCost 
101       99.5 
102       82.2 

[sas112]

I tried the hash approach, too. Works great. Thanks a lot.