topic proc sql multiplt left joins in SAS Programming

proc sql multiplt left joins

manya92 — Thu, 16 Aug 2018 14:31:53 GMT

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 ).

Re: proc sql multiplt left joins

AhmedAl_Attar — Thu, 16 Aug 2018 14:40:24 GMT

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

Re: proc sql multiplt left joins

Kurt_Bremser — Thu, 16 Aug 2018 14:49:58 GMT

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.

Re: proc sql multiplt left joins

ccaulkins9 — Thu, 09 May 2019 18:12:30 GMT

Touche, the Flash!