Numeric Format

[Rsadiq]

    Hello I am getting an error saying numeric format needed for my statements, I'm not sure where to apply these in my sql, I tried a couple of things before the where and, where the variables are listed and also in the statements but it did not work.

 where  (CHG_DTTM between "01Sep2017"d and "30Oct2017"d  )
                         and (CREAT_DTTM between "01Sep2017"d and "30Oct2017"d) 

     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                  format mmddyy10. 
          ,CHG_DTTM                   format mmddyy10.       
         ,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  (CHG_DTTM between "01Sep2017"d and "30Oct2017"d  )
                         and (CREAT_DTTM between "01Sep2017"d and "30Oct2017"d) 
              group by MBR_ID)) ;

     quit ;

[Tom]

SUM() will generate a number. Why would you attach the $6. format to a number?

For that matter why would you attach any format to it?  You might want to attach COMMA format if you expect values larger than 1000 and what to make them easier to read.

“The patient says "Doctor, it hurts when I do this." "Then don't do that!"”

Henny Youngman quotes

[Reeza · Accepted solution]

---

@Rsadiq wrote:

    Hello I am getting an error saying numeric format needed for my statements, 

---

When you have an error, post the exact error and the log.

[Rsadiq]

This is the error log
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:53 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                  format mmddyy10.
239                  ,CHG_DTTM                   format mmddyy10.
240                 ,case when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('1')  then 1
241                  when ASGN_TYP_ID like ('299') and ASGN_STS_TYP_ID in ('2')  then  1
242                                 else 0             end            format=$6.            as PHARMACY_CONSULT_CNT2
243                            from  sasdata.assignments_mbr
244                          where  (CHG_DTTM between "01Sep2017"d and "30Oct2017"d  )
245                                 and (CREAT_DTTM between "01Sep2017"d and "30Oct2017"d)
246                      group by MBR_ID)) ;
ERROR: Numeric expression requires a numeric format.
ERROR: Numeric expression requires a numeric format.
ERROR: Numeric expression requires a numeric format.

[Reeza]

Just to clarify, I was mentioning that more for clarity. With the log, it's very easy to come to the same answer @Tom did, you can't apply a character format to a variable. The error is also pretty clear in this case...

[Tom]

Just remove the 'FORMAT=$6' text that appears in three places in your query since you cannot attach a character format to numeric variables. And you generally do not WANT to attach formats to variables unless you NEED them.

Also your query does not really make any sense. The inner query is grouping by MBR_ID but not including it in the result set.  That might work, but the inclusion of the DISTINCT keyword means that if there are two or more different members that happen to have the same values for the three variables you did select then only one observation will be generated to pass into the outer query.

[Yavuz]

First you can try by remove all
format=$6.
Command.
If it dont gives error then ok.
Or
Secondly you can try to replace format=$6. with this format=commax16.