Converting the CTE recursive query to SAS logic

[Sathish_jammy]

I need to convert the given logic of recursive query in teradata to SAS programming.

WITH RECURSIVE fill_end as (
    SELECT *, DATEDIFF(l_dose, f_dose) + 1 as supply
    FROM (
        SELECT a.*, a.fill_dt as f_dose, 
               least(date_add(a.fill_dt, (a.fill_days_supply - 1)), 
                     (SELECT MAX(full_date) from tbl)) as l_dose, 
               1 as span 
        FROM final as a 
        WHERE a.rownum = 1
    ) as X
    UNION 
    SELECT *, DATEDIFF(l_dose, f_dose) + 1 as supply 
    FROM (
        SELECT a.*, 
               least(greatest(a.fill_dt, b.l_dose + 1), 
                     (SELECT MAX(full_date) + 1 from tbl)) as f_dose, 
               least(greatest(a.fill_dt, b.l_dose + 1) + fill_days_supply - 1, 
                     (SELECT MAX(full_date) from tbl)) as l_dose, 
               CASE WHEN datediff(a.fill_dt, b.l_dose) - 1 >= 60 
                    THEN b.span + 1 
                    ELSE b.span 
               END AS span 
        FROM final as a 
        INNER JOIN fill_end as b 
        ON a.id = b.id
        AND a.rownum = b.rownum + 1
    ) as X
)

Thanks in advance!

[PaigeMiller]

Please explain what this code is doing.

[Sathish_jammy]

This SQL query is creating a recursive Common Table Expression (CTE) named fill_end. It processes a table named final to calculate additional information about prescription fills, such as the first and last doses of a prescription period and an adjusted days supply.

Initial CTE (Base Case)
The first part of the CTE initializes the recursive process:
Select Columns: It selects all columns from the final table along with some calculated fields:
first_dose: This is set to the fill date (fill_sold_dt) of the prescription.
last_dose: This is calculated as the minimum of either the fill_sold_dt plus the fill_days_supply minus 1 or the maximum date from a table named tbl. Essentially, it ensures that the last dose does not exceed the maximum date in tbl.
span: A constant value of 1, used to track spans of prescription fills.
Filter Rows: It only considers the first row (rownum = 1) from the final table for each patient and medication combination.
Calculate adj_daysupp: This calculates the adjusted days supply as the difference between last_dose and first_dose plus 1.

Recursive Part
The recursive part of the CTE processes subsequent prescription fills:
Select Columns: It selects all columns from the final table along with some recalculated fields:
first_dose: This is the later of either the current fill date (fill_sold_dt) or the day after the last dose from the previous prescription fill (b.last_dose + 1), ensuring it does not exceed the maximum date plus one from tbl.
last_dose: This is the minimum of either the calculated first_dose plus the fill_days_supply minus 1 or the maximum date from tbl.
span: This increments by 1 if the gap between the current fill date and the previous last dose is 60 days or more, otherwise, it remains the same as the previous span.
Join Condition: The join condition matches prescription fills for the same patient (pat_id), same medication (gpi10), and consecutive rows (rownum and rownum + 1).
Calculate adj_daysupp: This calculates the adjusted days supply as the difference between last_dose and first_dose plus 1.

Summary
The CTE fill_end thus recursively processes each patient's prescription fills, adjusting the first_dose and last_dose dates to account for potential overlaps and gaps. It also calculates an adj_daysupp to reflect the adjusted days supply for each prescription period. This recursive CTE helps in creating a detailed and adjusted timeline of prescription fills for analysis.

[Tom]

So flesh this out a little:

The CTE fill_end thus recursively processes each patient's prescription fills, adjusting the first_dose and last_dose dates to account for potential overlaps and gaps. It also calculates an adj_daysupp to reflect the adjusted days supply for each prescription period. This recursive CTE helps in creating a detailed and adjusted timeline of prescription fills for analysis.

And we can begin to get a idea of what the problem is.  

Does each patient only have one type of prescription? Or do you also need to check for changes in medication?  Do you want to check for changes in dosage?

If the patient refills the prescription "early" (before the number of days supply for the previous fill are exhausted) do you want to remember those extra days so they can cover any future instances where the refill is "late" when calculating gaps in medication coverage?

[Tom]

You should just use a data step.  Then you can process the observations in order. 

So your data step might look something like:

data want;
  set final;
  by id ;
  if first.id then do; ..... end;
  ....
run;

For more detailed help please explain the actual problem (not the logic of the implementation using SQL features) with example input data and desired output for that input.