Missing comma?

[Rsadiq]

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 ;

[kiranv_]

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 ;

[Rsadiq]

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

[ballardw]

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

[Rsadiq]

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

[kiranv_]

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

[Rsadiq]

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       

[ballardw · Accepted solution]

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.

[kiranv_]

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

[Tom]

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 ;