BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rsadiq
Calcite | Level 5

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 ;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

9 REPLIES 9
kiranv_
Rhodochrosite | Level 12

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
Calcite | Level 5

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

ballardw
Super User

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
Calcite | Level 5

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_
Rhodochrosite | Level 12

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
Calcite | Level 5

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
Super User

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_
Rhodochrosite | Level 12

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

Tom
Super User Tom
Super User

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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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