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 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 ;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@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.

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

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

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

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

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

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
Quartz | Level 8
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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 8666 views
  • 0 likes
  • 4 in conversation