Translating complex SAS code into T-SQL

  • 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 */
    qualified_bed_days_recode=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;
    end;

    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:

    WITH cte1
    AS (
    SELECT *
    ,qualified_bed_days_recode_temp =
    CASE (episode_of_care_type)
    WHEN ('5') THEN
    CASE
    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)
    ELSE qualified_bed_days
    END
    ELSE qualified_bed_days
    END
    FROM tmp.delete_qualified_bed_days_recode
    ),
    cte2
    AS (
    SELECT *
    ,qualified_bed_days_recode =
    CASE (episode_of_care_type)
    WHEN ('5') THEN IIF(qualified_bed_days_recode_temp > ISNULL(episode_length_of_stay,0),episode_length_of_stay,qualified_bed_days_recode_temp)
    ELSE qualified_bed_days_recode_temp
    END
    FROM cte1
    )
    SELECT *
    FROM cte2
    -- 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?

    For example:

    CREATE FUNCTION dbo.udf_qualified_bed_days_recode
    (
    @episode_of_care_type CHAR(1)
    ,@episode_length_of_stay INT
    ,@qualified_bed_days INT
    ,@unqualified_bed_days INT
    )
    -- WITH ENCRYPTION, SCHEMABINDING, EXECUTE AS CALLER|SELF|OWNER|USER
    RETURNS INT
    AS
    BEGIN
    DECLARE @qualified_bed_days_recode INT = @qualified_bed_days;

    IF @episode_of_care_type != 5
    RETURN @qualified_bed_days_recode

    IF @unqualified_bed_days < 10
    BEGIN
    -- 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
    END
    RETURN @qualified_bed_days_recode
    END
    GO

    How would YOU implement this logic?  Pure T-SQL, function, or some other approach?

     

  • I had a go at simplifying your first piece of code. As I don't have the underlying table structures, I may have made some slight mistakes, but see whether you think that this is any better:

    SELECT *
    ,qualified_bed_days_recode = CASE
    WHEN qbdr.episode_of_care_type = '5'
    AND qbdrt.qualified_bed_days_recode_temp > ISNULL(
    qbdr.episode_length_of_stay
    ,0
    ) THEN
    qbdr.episode_length_of_stay
    ELSE
    qbdrt.qualified_bed_days_recode_temp
    END
    FROM tmp.delete_qualified_bed_days_recode qbdr
    CROSS APPLY
    (
    SELECT qualified_bed_days_recode_temp = CASE
    WHEN qbdr.episode_of_care_type = '5'
    AND ISNULL(qbdr.unqualified_bed_days, 0) <= 10 THEN
    IIF(
    ISNULL(qbdr.episode_length_of_stay, 0)
    - ISNULL(qbdr.unqualified_bed_days, 0) > 0
    ,ISNULL(qbdr.episode_length_of_stay, 0)
    - ISNULL(qbdr.unqualified_bed_days, 0)
    ,0)
    ELSE
    qualified_bed_days
    END
    ) qbdrt;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply