Desktop productivity for business analysts and programmers

Missing comma?

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Missing comma?

Hello I keep getting this error in my log saying I'm missing a comma.

I'm not sure where its missing I mean the log gives me the area where its missing but I cant seem to find out where it goes.

 


235                     from (select distinct
236                        sum(PHARMACY_CONSULT_CNT2)               format=$6.          as PHARMACY_CONSULT_CNT2
237                      from (select distinct
238                  CREAT_DTTM
239                 ,case when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('1')  then 1
240                  when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('2')  then  1
241                                 else 0             end            format=$6.            as PHARMACY_CONSULT_CNT2
242                                 and (a.CHG_DTTM between "01Sep2017"d and "30Oct2017"d)
                                    ___
                                    78
                                    76
ERROR 78-322: Expecting a ','.

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

243                

 

 

My code:

 


     proc sql ;
       create table pharmacy_consults2 as
        select distinct
            MBR_ID
            ,PHARMACY_CONSULT_CNT2                          format=$6.          as PHARMACY_CONSULT_CNT2
             from (select distinct
                sum(PHARMACY_CONSULT_CNT2)               format=$6.          as PHARMACY_CONSULT_CNT2
              from (select distinct
          CREAT_DTTM                            
         ,case when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('1')  then 1
          when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('2')  then  1
                         else 0             end            format=$6.            as PHARMACY_CONSULT_CNT2
                         and (a.CHG_DTTM between "01Sep2017"d and "30Oct2017"d)
                         and (a.CREAT_DTTM between "01Sep2017"d and "30Oct2017"d)
                    from  sasdata.assignments_mbr)
                   
              group by MBR_ID) ;
     quit ;


Accepted Solutions
Solution
‎11-03-2017 05:44 PM
Super User
Posts: 13,008

Re: Missing comma?

Your first from (select distinct does not have a matching )

so it's a tad difficult to tell what you are actually attempting but the second (select distinct ...occurs where it would be interpreted as an item on the select list for the first select distinct. Which is why a comma is expected.

 

 

View solution in original post


All Replies
PROC Star
Posts: 499

Re: Missing comma?

may it should be where statement instead of and

  proc sql ;
       create table pharmacy_consults2 as
        select distinct
            MBR_ID
            ,PHARMACY_CONSULT_CNT2                          format=$6.          as PHARMACY_CONSULT_CNT2
             from (select distinct
                sum(PHARMACY_CONSULT_CNT2)               format=$6.          as PHARMACY_CONSULT_CNT2
              from (select distinct
          CREAT_DTTM                            
         ,case when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('1')  then 1
          when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('2')  then  1
                         else 0             end            format=$6.            as PHARMACY_CONSULT_CNT2
           where  (a.CHG_DTTM between "01Sep2017"d and "30Oct2017"d)
                         and (a.CREAT_DTTM between "01Sep2017"d and "30Oct2017"d)
                    from  sasdata.assignments_mbr)
                   
              group by MBR_ID) ;
     quit ;
Contributor
Posts: 36

Re: Missing comma?

I did try switching to the where statement instead, but it still gave that error.

Super User
Posts: 13,008

Re: Missing comma?

Statement order is import. You cannot include a simple WHERE in a select statement, just as you cannot have a simple AND.

 

To simplify your original code you attempted to do something like this:

 

Proc sql;

    create table junk as

   select var1, var2 and x=3

   from otherjunk

   ;

 

May be you were looking for (in part)

(select distinct
          CREAT_DTTM                            
         ,case when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('1')  then 1
               when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('2')  then 1
               else 0             
          end  format=$6.  as PHARMACY_CONSULT_CNT2
      
         from  sasdata.assignments_mbr
             where (a.CHG_DTTM between "01Sep2017"d and "30Oct2017"d)
                and (a.CREAT_DTTM between "01Sep2017"d and "30Oct2017"d)
)

Or show the entire code with your where that failed

 

Contributor
Posts: 36

Re: Missing comma?

I tried the code below and it didn't seem to work, giving me a similar error

 


     proc sql ;
       create table pharmacy_consults2 as
        select distinct
            MBR_ID
            ,PHARMACY_CONSULT_CNT2                          format=$6.          as PHARMACY_CONSULT_CNT2
             from (select distinct
                sum(PHARMACY_CONSULT_CNT2)               format=$6.          as PHARMACY_CONSULT_CNT2
             (select distinct
          CREAT_DTTM                           
         ,case when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('1')  then 1
               when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('2')  then 1
               else 0            
          end  format=$6.  as PHARMACY_CONSULT_CNT2
     
         from  sasdata.assignments_mbr
             where (a.CHG_DTTM between "01Sep2017"d and "30Oct2017"d)
                and (a.CREAT_DTTM between "01Sep2017"d and "30Oct2017"d)


                   
              group by MBR_ID) ;
     quit ;

 

 

 

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     
NOTE: The PROCEDURE CONTENTS printed page 4.

229       
230             proc sql ;
231               create table pharmacy_consults2 as
232                select distinct
233                    MBR_ID
234                    ,PHARMACY_CONSULT_CNT2                          format=$6.          as PHARMACY_CONSULT_CNT2
12                                                         The SAS System                             16:02 Friday, November 3, 2017

235                     from (select distinct
236                        sum(PHARMACY_CONSULT_CNT2)               format=$6.          as PHARMACY_CONSULT_CNT2
237                     (select distinct
                        _
                        22
ERROR 22-322: Syntax error, expecting one of the following: ',', FROM. 

238                  CREAT_DTTM
239                 ,case when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('1')  then 1
240                       when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('2')  then 1
241                       else 0
242                  end  format=$6.  as PHARMACY_CONSULT_CNT2
243       
244                 from  sasdata.assignments_mbr
245                     where (a.CHG_DTTM between "01Sep2017"d and "30Oct2017"d)
246                        and (a.CREAT_DTTM between "01Sep2017"d and "30Oct2017"d)
247       
248       
249       
250                      group by MBR_ID) ;
                                          _
                                          79
ERROR 79-322: Expecting a ).

NOTE: PROC SQL set option NOEXEC and will continue t

PROC Star
Posts: 499

Re: Missing comma?

12                                                         The SAS System                             16:02 Friday, November 3, 2017

235                     from (select distinct
236                        sum(PHARMACY_CONSULT_CNT2)               format=$6.          as PHARMACY_CONSULT_CNT2
237                     (select distinct
                        _
                        22
ERROR 22-322: Syntax error, expecting one of the following: ',', FROM. 

 

a from clause is missing from your log

 

from (select distinct
     sum(PHARMACY_CONSULT_CNT2)               format=$6.          as PHARMACY_CONSULT_CNT2
from                  (select distinct

Contributor
Posts: 36

Re: Missing comma?

I do have from clauses in my code so I'm not sure why the error keeps occuring

 

 

 

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
     
NOTE: The PROCEDURE CONTENTS printed page 4.

229       
230             proc sql ;
231               create table pharmacy_consults2 as
232                select distinct
233                    MBR_ID
234                    ,PHARMACY_CONSULT_CNT2                          format=$6.          as PHARMACY_CONSULT_CNT2
12                                                         The SAS System                             16:10 Friday, November 3, 2017

235                     from (select distinct
236                        sum(PHARMACY_CONSULT_CNT2)               format=$6.          as PHARMACY_CONSULT_CNT2
237                      from (select distinct
238                  CREAT_DTTM
239                 ,case when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('1')  then 1
240                  when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('2')  then  1
241                                 else 0             end            format=$6.            as PHARMACY_CONSULT_CNT2
242                   where  (a.CHG_DTTM between "01Sep2017"d and "30Oct2017"d)
                      _____
                      78
                      76
ERROR 78-322: Expecting a ','.

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

243                                 and (a.CREAT_DTTM between "01Sep2017"d and "30Oct2017"d)
244                            from  sasdata.assignments_mbr)
245       
246                      group by MBR_ID) ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
247       

Solution
‎11-03-2017 05:44 PM
Super User
Posts: 13,008

Re: Missing comma?

Your first from (select distinct does not have a matching )

so it's a tad difficult to tell what you are actually attempting but the second (select distinct ...occurs where it would be interpreted as an item on the select list for the first select distinct. Which is why a comma is expected.

 

 

PROC Star
Posts: 499

Re: Missing comma?

you are so right, I think I too did the same mistake in the answer, as it lacked from clause in my answer

Super User
Super User
Posts: 7,845

Re: Missing comma?

Formatting can help a lot with figuring out what is going on.

You are referencing a variable MBR_ID that is not in any of the inputs.

Did you leave out another dataset that you were going to join with?  Perhaps that is what the two extra lines that start with AND are part of?

 

In addition do not assign character formats to numeric variables.

 

proc sql ;
create table pharmacy_consults2 as
  select distinct
    MBR_ID
   ,PHARMACY_CONSULT_CNT2
  from
   (select distinct sum(PHARMACY_CONSULT_CNT2) as PHARMACY_CONSULT_CNT2
    from
     (select distinct CREAT_DTTM
           , case when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('1') then 1
                  when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('2') then 1
                  else 0
             end as PHARMACY_CONSULT_CNT2
/* ?????????????
and (a.CHG_DTTM between "01Sep2017"d and "30Oct2017"d)
and (a.CREAT_DTTM between "01Sep2017"d and "30Oct2017"d)
????????????? */
      from  sasdata.assignments_mbr
     )
    group by MBR_ID
   )
;
quit ;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 286 views
  • 0 likes
  • 4 in conversation