Well, I don't think the SAS code is that complex, it's just that T-SQL (while I do like it) is a bit ahem "archaic"?
For example, in a SELECT statement, I really wish I could use the derivation of a column to the "left" in a subsequent derivation to the "right", instead of having to repeat verbose code.
In any case, here is my SAS code:
/* manual fix for qualified_bed_days */
if episode_of_care_type ='5' then do;
if unqualified_bed_days le 10 then
qualified_bed_days_recode = sum(episode_length_of_stay, - unqualified_bed_days);
if qualified_bed_days_recode = . or qualified_bed_days_recode < 0 then
qualified_bed_days_recode = 0;
if qualified_bed_days_recode > episode_length_of_stay then
qualified_bed_days_recode = episode_length_of_stay;
Comments on the SAS code:
Assume any of the columns could be NULL.
In SAS, the SUM function sums multiple columns (this is different than T-SQL), and handles nullability. For example, sum(5,-3)=2, sum(5,-null)=5, sum(null,-3)=-3, and sum(null,-null)=null.
For brevity, I won't expand on the rest of the logic. IMO it should be straightforward (even if you don't know SAS). I can update or reply to this post if needed.
This is the best T-SQL I can come up with so far:
WHEN ('5') THEN
WHEN (ISNULL(unqualified_bed_days,0) <= 10) THEN IIF(ISNULL(episode_length_of_stay,0) - ISNULL(unqualified_bed_days,0) > 0,ISNULL(episode_length_of_stay,0) - ISNULL(unqualified_bed_days,0),0)
WHEN ('5') THEN IIF(qualified_bed_days_recode_temp > ISNULL(episode_length_of_stay,0),episode_length_of_stay,qualified_bed_days_recode_temp)
-- WHERE qualified_bed_days_recode_temp != qualified_bed_days_recode
I could split this into a 3rd CTE, moving the first IIF to the 2nd CTE and removing a bit of nesting.
My "real" code is a view containing 4 CTE's with about 400 columns. I have saved the view to a table, and was surprised that querying the view has good performance vs. querying the table.
However, is this a scenario where I'd be better off creating a scalar function, where the language is a bit more robust?
CREATE FUNCTION dbo.udf_qualified_bed_days_recode
-- WITH ENCRYPTION, SCHEMABINDING, EXECUTE AS CALLER|SELF|OWNER|USER
DECLARE @qualified_bed_days_recode INT = @qualified_bed_days;
IF @episode_of_care_type != 5
IF @unqualified_bed_days < 10
-- This takes care of NULLs - @qualified_bed_days_recode cannot be NULL
SET @qualified_bed_days_recode = ISNULL(@episode_length_of_stay,0) - ISNULL(@unqualified_bed_days,0)
IF @qualified_bed_days_recode < 0
SET @qualified_bed_days_recode = 0
IF @qualified_bed_days_recode > ISNULL(@episode_length_of_stay,0)
SET @qualified_bed_days_recode = @episode_length_of_stay
How would YOU implement this logic? Pure T-SQL, function, or some other approach?