topic Two queries in SAS Programming

Two queries

Ivy — Fri, 01 Apr 2016 13:48:57 GMT

Hi,

I thought these two codes would return same results, however, the results were different with the duplicated records in the second query ( the original query data are all one patient having one record) .

I am wondering the reason. Thank you.

proc sql;
create table cohort as
select a.*
from lot2_2 as a
where a.patientid in
( select b.patientid
from &dblib..Lineoftherapy(where= (LineNumber = 1)) as b , &dblib..Demographics as c
where b.PatientID = c.PatientID and (year(b.StartDate) - c.BirthYear) >= 18 );
quit;

proc sql;
create table cohort as
select a.*
from lot2_2 as a , &dblib..Lineoftherapy(where= (LineNumber = 1)) as b , &dblib..Demographics as c
where ( a.patientid = b.PatientID = c.PatientID and (year(b.StartDate) - c.BirthYear) >= 18 );
quit;

Re: Two queries

LinusH — Fri, 01 Apr 2016 13:56:15 GMT

The first query is a subquery, and the result is Boolean (true or false).

The second is a join, the result is not restricted to true/false on the left table.

Re: Two queries

Ivy — Fri, 01 Apr 2016 17:20:51 GMT

Thank you, LinusH !

Re: Two queries

PGStats — Fri, 01 Apr 2016 18:32:52 GMT

The condition a.patientid = b.PatientID = c.PatientID isn't equivalent to (a.patientid = b.PatientID) and (b.PatientID = c.PatientID). It evaluates as (a.patientid = b.PatientID) = c.PatientID. Now, a.patientid = b.PatientID elaluates as TRUE or FALSE (1 or 0) which is then compared to c.PatientID. Unless c.PatientID is 0 or 1, the condition will be FALSE.

Replace a.patientid = b.PatientID = c.PatientID with (a.patientid = b.PatientID) and (b.PatientID = c.PatientID) and both queries should yield the same result.