I am not getting the specific result using the where function.

[Sandeep77]

Hi every one, I am looking for specific portfolios and I am using the where function but it is showing me all the portfolios and not just the one I have mentioned. My code is as below:

Proc sql ;
create table Test as
select distinct

tcfReferenceNumber as Accountnumber,
tcfNewPostcode,
tcfTraceResultType,
datepart(tcfDateConfirmed) as LIMA_date format date9.,
tcfDateConfirmed as LIMA_dt format datetime20.,

t3.Portfolioid,
t3.CaseflowPortfolioname,
t3.Accountstatus,
t3.Bookondate
from
(select
t1.tcfReferenceNumber,
t1.tcfTraceResultType,
t1.tcfDateConfirmed,
t2.trtDescription,
t1.tcfLivingasStated,
t1.tcfNewPostcode

from DBTrace.tCaseFlowDataAudit as t1
inner join DBTrace.tResultType as t2 on ( t1.tcfTraceResultType = t2.trtTypeID and t2.trtTraceSource ='LIMA' and t1.tcfTracerID = 99)
inner join dwhdw.dim_account as t3 on tcfReferenceNumber = t3.accountnumber

Where
(t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt));
where t3.CaseflowPortfolioname IN ("BT039", "EDF057", "EDF058", "EDF059", "EDF060", "BRITGAS129", "BRITGAS130");

quit;

I want only the highlighted portfolio name but it is showing me all. Can you please suggest?

[Kurt_Bremser]

WHERE is NOT a function. It is either a dataset option (wherever a dataset is referenced, with an equal sign) or a statement (in DATA or PROC steps) or a clause (in SQL).

Maxim 2: Read the Log.

You should find a message concerning this:

Where
(t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt)); /* this semicolon ends your CREATE TABLE statement */
where t3.CaseflowPortfolioname IN ("BT039", "EDF057", "EDF058", "EDF059", "EDF060", "BRITGAS129", "BRITGAS130"); /* standalone WHERE statements are not allowed in PROC SQL */

See this simple test log as reference:

 69         proc sql;
 70         create table test as
 71           select *
 72           from sashelp.class
 73           where sex = "M"
 74         ;
 NOTE: Table WORK.TEST created, with 10 rows and 5 columns.
 
 75         where name in ("Alfred");
 WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.

[PaigeMiller]

Where
(t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt));
where t3.CaseflowPortfolioname IN ("BT039", "EDF057", "EDF058", "EDF059", "EDF060", "BRITGAS129", "BRITGAS130");

quit;

You can't have two WHERE clauses in PROC SQL. This should work

Where
(t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt)) 
and t3.CaseflowPortfolioname IN ("BT039", "EDF057", "EDF058", "EDF059", "EDF060", "BRITGAS129", "BRITGAS130");

Note there is no semi-colon at the TimeStamp part, this would end the WHERE clause and is incorrect in this case.

From now on, when your code is not work, please show us the LOG (all of it for the PROC or DATA step that doesn't work, every single line for the PROC or DATA step that doesn't work, do not chop parts out).

[Sandeep77]

57 Where
58 (t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt))
59 and t3.CaseflowPortfolioname IN ("BT039", "EDF057", "EDF058", "EDF059", "EDF060", "BRITGAS129", "BRITGAS130");
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.

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

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

I am getting this error when I try using

Where
(t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt)) 
and t3.CaseflowPortfolioname IN ("BT039", "EDF057", "EDF058", "EDF059", "EDF060", "BRITGAS129", "BRITGAS130");

[Kurt_Bremser]

You must set your parentheses right. Do want your second WHERE to be part of the first (in the sub-select), then use AND instead of where and put it inside the brackets that enclose the sub-select. If you want it to be part of the outer SELECT, just remove the original semicolon.

[Kurt_Bremser]

And do always post the complete log of the whole step. Messages on their own provide not all necessary clues.

[PaigeMiller]

---

@Sandeep77 wrote:

57 Where
58 (t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt))
59 and t3.CaseflowPortfolioname IN ("BT039", "EDF057", "EDF058", "EDF059", "EDF060", "BRITGAS129", "BRITGAS130");
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.

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

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

I am getting this error when I try using

Where
(t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt)) 
and t3.CaseflowPortfolioname IN ("BT039", "EDF057", "EDF058", "EDF059", "EDF060", "BRITGAS129", "BRITGAS130");

---

Repeating, with emphasis: From now on, when your code does not work, please show us the LOG (all of it for the PROC or DATA step that doesn't work, every single line for the PROC or DATA step that doesn't work, do not chop parts out).

[Sandeep77]

I did tried making some changes with the syntax

Where
(t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt))
and (t3.CaseflowPortfolioname IN ('BT039', 'EDF057', 'EDF058', 'EDF059', 'EDF060', 'BRITGAS129', 'BRITGAS130'));

quit;

Below is the full log information: 

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Onboarding';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET
5 ! _CLIENTPROJECTPATH='C:\Users\sshukla\AppData\Roaming\SAS\EnterpriseGuide\8\AutoRecovery\f59f0216-093a-4b95-9535-5e4214562
5 ! bfe\Onboarding_(Recovered).egp';
6 %LET _CLIENTPROJECTPATHHOST='LWLT5CG9322XFL';
7 %LET _CLIENTPROJECTNAME='Onboarding_(Recovered).egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 ODS LISTING GPATH=&sasworklocation;
18 FILENAME EGHTML TEMP;
19 ODS HTML5(ID=EGHTML) FILE=EGHTML
20 OPTIONS(BITMAP_MODE='INLINE')
21 %HTML5AccessibleGraphSupported
22 ENCODING='utf-8'
23 STYLE=HTMLBlue
24 NOGTITLE
25 NOGFOOTNOTE
26 GPATH=&sasworklocation
27 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28

29 Proc sql ;
30 create table LIMA as
31 select distinct
32
33 tcfReferenceNumber as debt_code,
34 tcfNewPostcode,
35 tcfTraceResultType,
36 datepart(tcfDateConfirmed) as LIMA_date format date9.,
37 tcfDateConfirmed as LIMA_dt format datetime20.,
38 trtDescription,
39 tcfLivingasStated,
40 t3.Portfolioid,
41 t3.CaseflowPortfolioname,
42 t3.Accountstatus,
43 t3.Bookondate
44 from
45 (select
46 t1.tcfReferenceNumber,
47 t1.tcfTraceResultType,
48 t1.tcfDateConfirmed,
49 t2.trtDescription,
50 t1.tcfLivingasStated,
51 t1.tcfNewPostcode
52
53 from DBTrace.tCaseFlowDataAudit as t1
2 The SAS System 11:36 Tuesday, March 15, 2022

54 inner join DBTrace.tResultType as t2 on ( t1.tcfTraceResultType = t2.trtTypeID and t2.trtTraceSource ='LIMA' and
54 ! t1.tcfTracerID = 99)/*<- here we are selecting only LIMA*/
55 inner join dwhdw.dim_account as t3 on tcfReferenceNumber = t3.accountnumber
56
57 Where
58 (t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and t1.tcfTimeStamp < '30NOV2021:00:00:00'dt))
59 and (t3.CaseflowPortfolioname IN ('BT039', 'EDF057', 'EDF058', 'EDF059', 'EDF060', 'BRITGAS129', 'BRITGAS130'));
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: ), ','.

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

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
60
61 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 347.62k
OS Memory 29784.00k
Timestamp 03/15/2022 01:36:28 PM
Step Count 18 Switch Count 0

62
63 %LET _CLIENTTASKLABEL=;
64 %LET _CLIENTPROCESSFLOWNAME=;
65 %LET _CLIENTPROJECTPATH=;
66 %LET _CLIENTPROJECTPATHHOST=;
67 %LET _CLIENTPROJECTNAME=;
68 %LET _SASPROGRAMFILE=;
69 %LET _SASPROGRAMFILEHOST=;
70
71 ;*';*";*/;quit;run;
72 ODS _ALL_ CLOSE;
73
74
75 QUIT; RUN;
76

[Kurt_Bremser]

Writing spaghetti code is not conducive to easy debugging.

I formatted your code, so the issue becomes visible:

proc sql ;
create table LIMA as
  select distinct
    tcfReferenceNumber as debt_code,
    tcfNewPostcode,
    tcfTraceResultType,
    datepart(tcfDateConfirmed) as LIMA_date format date9.,
    tcfDateConfirmed as LIMA_dt format datetime20.,
    trtDescription,
    tcfLivingasStated,
    t3.Portfolioid,
    t3.CaseflowPortfolioname,
    t3.Accountstatus,
    t3.Bookondate
  from (
    select
      t1.tcfReferenceNumber,
      t1.tcfTraceResultType,
      t1.tcfDateConfirmed,
      t2.trtDescription,
      t1.tcfLivingasStated,
      t1.tcfNewPostcode
    from DBTrace.tCaseFlowDataAudit as t1
    inner join DBTrace.tResultType as t2
    on (
      t1.tcfTraceResultType = t2.trtTypeID and t2.trtTraceSource ='LIMA' and
      t1.tcfTracerID = 99
    )/*<- here we are selecting only LIMA*/
    inner join dwhdw.dim_account as t3
    on tcfReferenceNumber = t3.accountnumber
    where (
      t1.tcfTimeStamp >= '01NOV2021:00:00:00'dt and
      t1.tcfTimeStamp < '30NOV2021:00:00:00'dt)
  )
  and ( /* this AND becomes part of the outer SELECT, where it is not allowed */
    t3.CaseflowPortfolioname IN ('BT039', 'EDF057', 'EDF058', 'EDF059', 'EDF060', 'BRITGAS129', 'BRITGAS130')
  )
;

Also see my previous post. If you want the IN condition to work in the sub-select, you must put it there.

[PaigeMiller]

You are getting an error which appears after your WHERE clause

Examine your WHERE clause carefully. Are the number of left parentheses equal to the number of right parentheses?