Lapis Lazuli | Level 10

## Converting the CTE recursive query to SAS logic

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,
(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
)```

5 REPLIES 5
Diamond | Level 26

## Re: Converting the CTE recursive query to SAS logic

Please explain what this code is doing.

--
Paige Miller
Lapis Lazuli | Level 10

## Re: Converting the CTE recursive query to SAS logic

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.

Super User

## Re: Converting the CTE recursive query to SAS logic

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?

Super User

## Re: Converting the CTE recursive query to SAS logic

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.

Meteorite | Level 14

## Re: Converting the CTE recursive query to SAS logic

It looks like your code can be converted to SAS data step code like this:

1. Get the FULL_DATE from TBL:

``````Proc sql noprint;
SELECT MAX(full_date) into :full_date from tbl;
quit;``````

2. Process the data:

``````data want;
set final;
by id rownum;
if rownum=1 then do;
f_dose=fill_dt;
l_dose=min(fill_dt+fill_days_supply-1,&full_date);
span=1;
end;
else do;
f_dose=min(max(fill_dt,l_dose+1),&full_date+1);
if fill_dt-l_dose>=60 then
span+1;
l_dose=min(max(fill_dt,l_dose+1)+fill_days_supply-1,&full_date);
end;
retain l_dose span;
format l_dose f_dose date9.;
run;``````

The code shown assumes that the tables exist in SAS.WORK, and that the dates are SAS date values. The FINAL table should be sorted by ID and ROWNUM. The code also assumes that rows exist for consecutive ROWNUM values (1,2,3...) for all IDs.

Discussion stats
• 5 replies
• 459 views
• 0 likes
• 4 in conversation