topic Re: proc sql: multiple conditions at where in SAS Programming

proc sql: multiple conditions at where

sas112 — Sun, 07 Feb 2021 01:37:39 GMT

I have a dataset below. I would like to find the total payment of each EFAMID. The 
total payment is calculated as Pay during 60 days prior to and 90 days after the service (OpCode=12)
date. For example, EFAMID=101, the total payment is 10.7 + 18.0 + 10.2 = 38.9 because
the following observations are within 60 days prior to and 90 days after the 
01/14/2013 (the SvcDate of its OpCode=12).

101 01/13/2013 19 10.7
101 01/14/2013 12 18.0
101 04/16/2013 13 10.2

I try to set the filtering conditions at where, but it is not correct. How do I specify
multiple conditions at where? Thanks in advance.

data have;
input EFAMID $ SvcDate :mmddyy10. OpCode :$2. Pay;
format svcdate yymmdd10.;
datalines;
101 09/03/2012 10 11.0
101 01/13/2013 19 10.7
101 01/14/2013 12 18.0
101 04/16/2013 13 10.2
101 11/10/2013 19 10.0
102 02/10/2013 10 9.7
102 02/12/2013 12 17.5
102 02/13/2013 15 10.2
;


proc sql;
  create table want as
  select a.EFAMID,  
  (
    select sum(Pay)
    from have c
    where
      a.EFAMID = c.EFAMID and 
      abs(a.SvcDate - c.SvcDate) le 60 or ((c.SvcDate gt a.SvcDate) and (c.SvcDate - a.SvcDate le 90)) 
  ) as THU
  from have a
  where a.opcode = '12'
;
quit;

Re: proc sql: multiple conditions at where

mkeintz — Sun, 07 Feb 2021 04:58:22 GMT

@sas112 wrote:

I have a dataset below. I would like to find the total payment of each EFAMID. The 
total payment is calculated as Pay during 60 days prior to and 90 days after the service (OpCode=12)
date. For example, EFAMID=101, the total payment is 10.7 + 18.0 + 10.2 = 38.9 because
the following observations are within 60 days prior to and 90 days after the 
01/14/2013 (the SvcDate of its OpCode=12).

101 01/13/2013 19 10.7
101 01/14/2013 12 18.0
101 04/16/2013 13 10.2

...
I try to set the filtering conditions at where, but it is not correct.
...

But 4/16/2013 is NOT within 90 days of the opcode 12 date of 1/14/2013. It is 92 days later, so should not be included in your total for EFAMID 101. If by "it is not correct" you mean your total for 101 is only 28.7, then it is correct.

BTW, here is a data step solution. It assumes:

The data are sorted by EFAMID (but not necessarily svcdate within EFAMID)
You never have more than 1 instance of opcode 12 for any id.

data have;
input EFAMID $ SvcDate :mmddyy10. OpCode :$2. Pay;
format svcdate yymmdd10.;
datalines;
101 09/03/2012 10 11.0
101 01/13/2013 19 10.7
101 01/14/2013 12 18.0
101 04/16/2013 13 10.2
101 11/10/2013 19 10.0
102 02/10/2013 10 9.7
102 02/12/2013 12 17.5
102 02/13/2013 15 10.2
;

data want (keep=EFAMID OPCODE12_DATE THU);
  merge have
        have (where=(opcode='12') rename=(svcdate=opcode12_date)) ;
  by efamid;
  if first.efamid then THU=0;

  if (opcode12_date-60) <= svcdate and svcdate <= (opcode12_date+90) then THU+pay;

  if last.efamid;
run;

Re: proc sql: multiple conditions at where

sas112 — Sun, 07 Feb 2021 05:26:28 GMT

Thanks for point out

But 4/16/2013 is NOT within 90 days of the opcode 12 date of 1/14/2013. It is 92 days later, so should not be included in your total for EFAMID 101.

You are right. 4/16/2013 is NOT within 90 days of the opcode 12 date of 1/14/2013.

I need to set the conditions at proc sql since it is just part of my process. Do you have any idea how to specify those conditions at proc sql? Thanks.

Re: proc sql: multiple conditions at where

PGStats — Sun, 07 Feb 2021 05:46:57 GMT

With SQL you could do:

proc sql;
select 
    a.efamid, a.svcDate,
    sum(b.Pay) as totalPay
from
    have as a inner join
    have as b on a.efamid=b.efamid and intck('day', a.svcDate, b.svcDate) between -60 and 90
where a.opCode = "12"
group by a.efamid, a.svcDate;
quit;

Re: proc sql: multiple conditions at where

sas112 — Sun, 07 Feb 2021 15:40:05 GMT

intck('day', a.svcDate, b.svcDate) between -60 and 90

is what I need. Thanks a lot.