topic Re: multiple where statement in New SAS User

multiple where statement

Sandeep77 — Wed, 06 Apr 2022 08:26:25 GMT

Hi, I am trying to add the names in the where statement but getting error.

Proc sql ;
create table Test as
select distinct

debt_code,
ResultType,
datepart(tcfDateConfirmed) as Test_date format date9.,
DateConfirmed as Test_dt format datetime20.,
Description,
t3.Portfolioid,
t3.CaseflowPortfolioname,
t3.Accountstatus,
t3.Bookondate
from
(select
t1.ReferenceNumber,
t1.TraceResultType,
t1.DateConfirmed,
t2.Description

from Trace.CaseFlowData as t1
inner join Trace.ResultType as t2 on ( t1.TraceResultType = t2.TypeID and t2.TraceSource ='Test' and t1.TracerID = 99)/
inner join Test_account as t3 on ReferenceNumber = t3.accountnumber

Where
(t1.TimeStamp >= '01DEC2021:00:00:00'dt and t1.TimeStamp < '31DEC2021:00:00:00'dt) and
t3.Bookondate >= '01DEC2021:00:00:00'dt and t3.Bookondate < '31DEC2021:00:00:00'dt) and
t3.CaseflowPortfolioname like ('BT039', 'EDF058', 'EDF060', 'EDF059', 'BRITGAS129', 'EDF057', 'BRITGAS129');
quit;

When I add the bold line in the where clause to get only these portfolionames, I am getting below error:

54 from Trace.tCaseFlowDataAudit as t1
55 inner join Trace.tResultType as t2 on ( t1.TraceResultType = t2.TypeID and t2.TraceSource ='Test' and
55 ! t1.TracerID = 99)
56 inner join Test_account as t3 on ReferenceNumber = t3.accountnumber
57
58 Where
59 (t1.TimeStamp >= '01DEC2021:00:00:00'dt and t1.TimeStamp < '31DEC2021:00:00:00'dt) and
60 t3.CaseflowPortfolioname like ('BT039', 'EDF058', 'EDF060', 'EDF059', 'BRITGAS129', 'EDF057', 'BRITGAS129');
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
61 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):

Re: multiple where statement

Sandeep77 — Wed, 06 Apr 2022 08:46:18 GMT

I also tried t3.CaseflowPortfolioname IN ('BT039', 'EDF058', 'EDF060', 'EDF059', 'BRITGAS129', 'EDF057', 'BRITGAS129') but getting same error.

Re: multiple where statement

Kurt_Bremser — Wed, 06 Apr 2022 08:47:21 GMT

Code formatting.

As long as you write ugly, unstructured code like this, you'll have trouble seeing the possible problems.

And you can use the Enhanced Editor to detect the relationship of parentheses.

After applying indentation according to the brackets, I get this:

Proc sql ;
create table Test as
  select distinct
    debt_code,
    ResultType,
    datepart(tcfDateConfirmed) as Test_date format date9.,
    DateConfirmed as Test_dt format datetime20.,
    Description,
    t3.Portfolioid,
    t3.CaseflowPortfolioname,
    t3.Accountstatus,
    t3.Bookondate
    from (
      select
        t1.ReferenceNumber,
        t1.TraceResultType,
        t1.DateConfirmed,
        t2.Description
      from Trace.CaseFlowData as t1
      inner join Trace.ResultType as t2
      on ( t1.TraceResultType = t2.TypeID and t2.TraceSource ='Test' and t1.TracerID = 99)
      inner join Test_account as t3 on ReferenceNumber = t3.accountnumber
      Where 
        (t1.TimeStamp >= '01DEC2021:00:00:00'dt and t1.TimeStamp < '31DEC2021:00:00:00'dt) and
        t3.Bookondate >= '01DEC2021:00:00:00'dt and t3.Bookondate < '31DEC2021:00:00:00'dt 
    ) and
    t3.CaseflowPortfolioname like ('BT039', 'EDF058', 'EDF060', 'EDF059', 'BRITGAS129', 'EDF057', 'BRITGAS129')
;
quit;

You can see that a bracket terminates the sub-select, causing the rest of the WHERE condition to be invalid syntax.

So I tried to correct your code:

proc sql;
create table Test as
  select distinct
    debt_code,
    ResultType,
    datepart(tcfDateConfirmed) as Test_date format date9.,
    DateConfirmed as Test_dt format datetime20.,
    Description,
    t3.Portfolioid,
    t3.CaseflowPortfolioname,
    t3.Accountstatus,
    t3.Bookondate
    from (
      select
        t1.ReferenceNumber,
        t1.TraceResultType,
        t1.DateConfirmed,
        t2.Description
      from Trace.CaseFlowData as t1
      inner join Trace.ResultType as t2
      on t1.TraceResultType = t2.TypeID and t2.TraceSource = 'Test' and t1.TracerID = 99
      inner join Test_account as t3 on ReferenceNumber = t3.accountnumber
      where 
        t1.TimeStamp >= '01DEC2021:00:00:00'dt and t1.TimeStamp < '31DEC2021:00:00:00'dt and
        t3.Bookondate >= '01DEC2021:00:00:00'dt and t3.Bookondate < '31DEC2021:00:00:00'dt and
        t3.CaseflowPortfolioname in ('BT039','EDF058','EDF060','EDF059','BRITGAS129','EDF057','BRITGAS129')
    )

;
quit;

correct placement of parentheses
removed unnecessary parentheses (all your logical operators are AND)
changed LIKE to IN
ask yourself if DISTINCT is needed at all; having to do a sort of the result over all variables to detect doubles causes a massive (and usually unnecessary) load

Re: multiple where statement

Sandeep77 — Wed, 06 Apr 2022 08:54:05 GMT

Thank you