CODEWITHSUNDEEP
datesascoalesce
nhandy
nhandy

Reputation: 83

SAS and Coalesce function to insert missing date values?

I have a data set (VISIT1) of patient visit dates (PROC1_DT) with a date that is 90-days prior (LOOKBACK_DT). I have joined this VISIT1 with another table VISIT2, such that if a PROC2_DT falls in between PROC1_DT and LOOKBACK_DT, the new list is created.

However, I would like to have all dates from VISIT1 to appear in the final data set, even if, there is no PROC2_DT which falls in between PROC1_DT and LOOKBACK_DT. For example, Doctor B and Patient 5 don't appear in the CURRENT FINAL DATA SET because the patient had no PROC2_DT that fell in between PROC1_DT and LOOKBACK_DT.

I attempted to use COALESE but, I'm obviously not using it properly and/or need another method.

VISIT1
Doctor Patient LOOKBACK_DT PROC1_DT
A      1       28APR2018   27JUL2018
A      2       07MAR2018   05JUN2018
A      3       19JUN2018   17SEP2018
A      4       22MAY2018   20AUG2018
B      5       07FEB2019   08MAY2019
C      6       14JUL2018   12OCT2018
C      7       10APR2018   09JUL2018
C      8       17NOV2018   15FEB2019

proc sql;
  create table final_data_set as
  select distinct 
      a.Doctor 
    , a.Patient 
    , a.LOOKBACK_DT
    , coalesce(b.viisit_dt) as PROC2_DT format=date9.
    , a.PROC1_DT
  from
    VISIT1 a
  left join
    ( select *
      from prvdr_bene_visit
      where _proc2 ge 1
    ) b 
  on a.Patient=b.Patient
  where
    b.VISIT_DT ge a.lookback_dt
    and b.VISIT_DT lt a.PROC1_DT
  group by  
      a.Doctor
    , a.Patient
    , a.PROC1_DT
    , a.lookback_dt
  order by
    a.Doctor
    , a.Patient
    , a.PROC1_DT
  ;
quit;

FINAL_DATA_SET 
Doctor Patient LOOKBACK_DT PROC2_DT   PROC1_DT
A      1       28APR2018   24JUL2018  27JUL2018
A      2       07MAR2018   03JUN2018  05JUN2018
A      3       19JUN2018   07SEP2018  17SEP2018
A      4       22MAY2018   18AUG2018  20AUG2018
C      6       14JUL2018   09OCT2018  12OCT2018
C      7       10APR2018   03JUL2018  09JUL2018
C      8       17NOV2018   28DEC2018  15FEB2019

Upvotes: 0

Views: 2104

Answers (1)

vasja
vasja

Reputation: 4792

  • Make it indeed an LEFT JOIN:

If I understand the task correctly, your problem is that you think are using LEFT JOIN but you're actually doing inner join. This is because you added WHERE condition which uses column from "the left joined table" in a way that requires the records to match - which is effectivelly an inner join.

To make it Left join you need to make the condition provided in WHERE to be a part of JOIN conditions - this is here as easy as changing WHERE to AND:

on a.Patient=b.Patient
/* where */ and
b.VISIT_DT ge a.lookback_dt
and b.VISIT_DT lt a.PROC1_DT
  • Regarding coalesce used for PROC2_DT date - I'm not sure what's your plan there. Coalesce is a row-based operation, which returns first non-null value from several arguments, e.g. coalesce(b.VISIT_DT, a.PROC_DT) as some_date

It's not an aggregate function and there is no other aggregate function so the GROUP BY clause is not needed.

Upvotes: 1

Related Questions