topic sql join matching one variable to multiple in SAS Programming

sql join matching one variable to multiple

Batman — Thu, 16 Mar 2023 11:56:12 GMT

I'm trying to get a unique patient count based on the presence of certain Diagnosis codes in multiple diagnosis code columns. It has to be done in an sql query. This code works, but I'm wondering if there is a more efficient method.

PROC SQL;

SELECT (COUNT(distinct t1.GEO_BENE_SK)) AS COUNT_of_GEO_BENE_SK

FROM MDCRD_AL.V2_FCT_CLM t1 /*CLAIMS FILE*/

INNER JOIN WORK.ICD10 t2 ON /* LIST OF DIAGNOSIS CODES*/

(

t1.CLM_DGNS_1_CD = t2.F1

or t1.CLM_DGNS_2_CD = t2.F1

or t1.CLM_DGNS_3_CD = t2.F1

or t1.CLM_DGNS_4_CD = t2.F1

)

WHERE t1.CLM_THRU_DT BETWEEN '1Jan2016'd AND '31Dec2016'd

AND t1.CLM_TYPE_CD = 60 AND t1.CLM_FINL_ACTN_IND = 'Y';

QUIT;

Re: sql join matching one variable to multiple

s_lassen — Tue, 21 Mar 2023 10:20:13 GMT

You may get some performance enhancement by using an EXISTS query (as the inner join may be returning more than one row from the diagnosis table):

PROC SQL;
   SELECT (COUNT(distinct t1.GEO_BENE_SK)) AS COUNT_of_GEO_BENE_SK
      FROM MDCRD_AL.V2_FCT_CLM t1  /*CLAIMS FILE*/
      WHERE t1.CLM_THRU_DT BETWEEN '1Jan2016'd AND '31Dec2016'd
          AND t1.CLM_TYPE_CD = 60 AND t1.CLM_FINL_ACTN_IND = 'Y'
          and exists(select * from WORK.ICD10 t2 ON  /* LIST OF DIAGNOSIS CODES*/ where 
                              t1.CLM_DGNS_1_CD = t2.F1
                              or t1.CLM_DGNS_2_CD = t2.F1
                              or t1.CLM_DGNS_3_CD = t2.F1
                              or t1.CLM_DGNS_4_CD = t2.F1
                                             );

 QUIT;

With an EXISTS query, the program should return as soon as it finds the first matching row, and that may speed things up (with the inner join, SQL will insist on fetching all matching rows).

Re: sql join matching one variable to multiple

Kurt_Bremser — Tue, 21 Mar 2023 11:46:51 GMT

"Efficient" in terms of simpler code or better performance?

Re: sql join matching one variable to multiple

Batman — Tue, 21 Mar 2023 11:56:45 GMT

Either, but mostly I'm hoping to speed things up, so I guess better performance is the priority.

Re: sql join matching one variable to multiple

Kurt_Bremser — Tue, 21 Mar 2023 13:13:28 GMT

@Batman wrote:

Either, but mostly I'm hoping to speed things up, so I guess better performance is the priority.

Then you must look at non-SQL solutions. For the lookup, loading the wanted diagnosis codes into a hash object will outperform any SQL join.

Depending on the cardinality of geo_bene_sk, even the COUNT DISTINCT might be doable in a hash, without any previous sorting.