Proc SQL error

[req41273]

Anyone see why I would be getting syntax error on proc sql code attached.  Error getting is:

28         MP.PHNUMBER AS NEHII_PHNUMBER,
29         ((select MP.PHNUMBER FROM MDM.MPI_MEMPHONEWC WHERE ATTRRECNO = 389), ' ' ) AS (HOME_NUMBER),
                                                                              _
                                                                              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, ^, ^=, |, ||, ~, ~=. 

389 is a numeric field type. Entire code is attached.

[ballardw]

You code in the attached document does not match the code you show:

from the document attachment:

nvl((select MP.PHNUMBER FROM MDM.MPI_MEMPHONEWC WHERE ATTRRECNO = 389), ' ' ) AS HOME_NUMBER,

which appears to be a call to a function not supported in SAS.

You should paste the log of the entire procedure including the error messages into a code box opened using the forum's {I} or "running man" icon to preserve text layout. The underscore with the 22 should appear under the location SAS detected the syntax issue.

If there is NO function in your actual code as below

then what is the purpose of the code over the ^^^ I have placed below?

 ((select MP.PHNUMBER FROM MDM.MPI_MEMPHONEWC WHERE ATTRRECNO = 389), ' ' )
                                                                    ^^^^^^

[req41273]

I've attached the sas error log for review.  I have removed nvl statement from the code as SAS didn't recognize this function.  Still getting syntax error.  The ( ) after 389 and 391 are to try to state to put parenthesis around the values as they are phone numbers area codes in that field.

[ballardw]

That attachment doc 8.docs is not a log. It looks exactly like the previous "doc 8.docx" which was supposedly code but showing call to the NVL function that SAS does not use/recognize.

Copy from the LOG and paste into a code box. That has to be much easier than creating a word processor document and attaching. Plus, word processor documents will also do funny things to code and log entries.

If you are attempting to match a value like (389) that has parentheses as part of the value then that is a character value and would have to appear inside quotes such as

= '(389)'

Your "code" does not show open and close parentheses so I am not quite sure where you think you are using such.

[req41273]

Where is the code box at? 

[Kurt_Bremser]

---

@req41273 wrote:

Where is the code box at? 

---

When posting in Rich Text mode, it's the 6th ({i} and 7th (running man) icon on top of the posting window.

[req41273]

Any thought on why I'm getting a error 22-322 on my code below?

          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTNAME='';
7          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=ACTIVEX;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS%20EG%209.4.1/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         option obs=max;
25         


26         proc sql noprint /*INOBS=100*/;
27         create table MDM_Members1 as
28         
29         SELECT DISTINCT
30         MI.IDNUMBER AS SUBSCRIBER_ID
31         28         ,MP.PHNUMBER as N_PHNUMBER
           __
           22
           76
ERROR 22-322: Syntax error, expecting one of the following: ',', LEN, LENGTH.  

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

32         29         ,(select MP.PHNUMBER FROM MDM.MPI_MEMPHONEWC MP WHERE MP.MATTRRECNO = MA.MEMRECNO = MI.MEMRECNO AND
32       ! MP.ATTRRECNO = 389) AS HOME_NUMBER,
33         30         (select MP.PHNUMBER FROM MDM.MPI_MEMPHONEWC MP WHERE MP.MATTRRECNO = MA.MEMRECNO = MI.MEMRECNO AND
33       ! MP.ATTRRECNO = 391) AS MOBILE_NUMBER,
34         31         ,MA.STLINE1 AS NEHII_STREET_ADDRESS1
35         32         ,MA.CITY AS NEHII_CITY
36         33         ,MA.STATE AS NEHII_STATE
37         34         ,MA.COUNTY AS NEHII_COUNTY
38         35         ,MA.ZIPCODE AS NEHII_ZIPCODE
39         36         FROM MDM.MPI_MEMPHONEWC MP, MDM.MPI_MEMADDRWC MA, MDM.MPI_MEMIDENTWC MI
40         37         WHERE /* MP.RECSTAT ='A' AND MI.RECSTAT='A' AND */
41         38         MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO
42         39         AND MP.ATTRRECNO IN (389,391)
43         40         AND MP.MEMRECNO = MA.MEMRECNO
44         41         AND MP.MEMRECNO = MA.MEMRECNO AND MA.ATTRRECNO = 390;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
45         
2                                                          The SAS System                              09:06 Wednesday, May 29, 2019

46         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
47         run;
48         
49         
50         GOPTIONS NOACCESSIBLE;
51         %LET _CLIENTTASKLABEL=;
52         %LET _CLIENTPROCESSFLOWNAME=;
53         %LET _CLIENTPROJECTPATH=;
54         %LET _CLIENTPROJECTNAME=;
55         %LET _SASPROGRAMFILE=;
56         
57         ;*';*";*/;quit;run;
58         ODS _ALL_ CLOSE;

[Tom · Accepted solution]

Looks like you pasted line numbers (28,29,etc) into your code.  Remove those.