proc sql multiplt left joins

[manya92]

Hi all, I am trying to left join one table with two tables to get multiple variables. I wrote some code but its showing me an error. Any help is appreciated.

 proc sql;
 437         create table _02a_nl as
 438         select distinct a.*,b.labcomponent,b.TestResultCleaned,c.labcomponent,c.TestResultCleaned,neutrophil,lymphocyte
 439         ,b.TestDate as neu_date format mmddyy10. ,c.TestDate as lymp_date format mmddyy10.,
 440         case
 441         when b.labcomponent = 'Neutrophil Count (absolute)' and b.TestResultCleaned ne . then b.TestResultCleaned
 442         else .
 443         end as neutrophil,
 444         case
 445         when c.labcomponent = 'Lymphocyte Count Absolute' and c.TestResultCleaned ne . then c.TestResultCleaned
 446         else .
 447         end as lymphocyte
 448         from _02a_insurance as a
 449         left join dfi_panc.lab as b
 450         on a.PatientID = b.PatientID
 451         left join dfi_panc.lab as c
 452         on a.PatientID=c.PatientID
 453         and ((a.index_date -30) le b.TestDate le (a.index_date)) AND (b.labcomponent = ('Neutrophil Count (absolute)')
 454         AND ((a.index_date -30) le c.TestDate le (a.index_date)) AND (c.labcomponent =('Lymphocyte Count Absolute'))
 455        order by a.PatientID,b.TestDate,c.TestDate;
             _____
             79
 ERROR 79-322: Expecting a ).
 

[AhmedAl_Attar · Accepted solution]

Hi @manya92

The error is from these two lines

and ........ AND (b.labcomponent = ('Neutrophil Count (absolute)')  /* This could be missing a closing )! */
AND ........ AND (c.labcomponent =('Lymphocyte Count Absolute')) /* This could be using the wrong syntax? */

I think you need to revise either or both

Hope this helps,

Ahmed

[ccaulkins9]

Touche, the Flash!

[Kurt_Bremser]

In the Enhanced Editor, place the cursor after the last closing bracket. The Editor will highlight this bracket and the one opening bracket it sees as corresponding. This will give you a clue where a closing bracket is missing.