BookmarkSubscribeRSS Feed
Calcite | Level 5

Hi everyone,

A customer is using SAS 9.4 on-premise and connecting with CData ODBC Driver for Amazon Athena.

The customer is from South Korea. Including that because the locales might affect the output just in case.

I'm using SAS Viya free trial for testing purposes since SAS 9.4 doesnt have any free trial from what I've searched. 

The customer has 2 problems:

1) DATE values in Athena such as :

2020-10-11 show in SAS as 11OCT2020

YYYY-MM-DD format shows in SAS as DDMMMYY.

For this, I believe this DDMMMYY is the default date format in SAS. However, I want to be able to make the YYMMDD format by default whenever I get data from this driver, such as anytime I connect these DATE values show in YYMMDD format in SAS. How can I do this?


2) DECIMAL values in Athena, such as 42000000.00, 30099090.00, 395929 show as DOLLAR format in SAS. I read in SAS documentation that the default format for numeric values is BEST format. I havent made any changes in my SAS. Why is DOLLAR showing as default? Could it be a driver problem? And most importantly, how can I make the BEST format my permanent default format for decimal values? 

I've seen some solutions researching but they mostly include formatting the columns in my query in the .sas program, so each time you run it it changes the format. However, I want to make it by default so I don't need to do all these extra steps in my code.


Could you please help me?

If you need any additional details, feel free to ask me.

Thank you in advance.

Super User Tom
Super User

It shouldn't really matter how the values are DISPLAYED as long as the right values are stored.

Just issue a FORMAT statement to change to a different format.


To display date value in YYYY-MM-DD style then use the YYMMDD10. format.


BEST is probably NOT the right format to use for a value that was DECIMAL in the database.  That can only have a FIXED number of decimal digits.  So use the normal numeric format instead.  So if original variable (field/column) could store 8 digital to the left of the decimal place and two to the right then use then 11.2 format.  You can use the F alias for the normal numeric format if you want to remind yourself that it is fixed length.   If the values could be negative with the same magnitude you will need to add one to the total width to account for the leading dash character to indicate a negative value.


data want;
  set athena.mydata;
  format mydate yymmdd10.  mydecimal F12.2 ;
Calcite | Level 5

Thank you for your help.

Your solution definitely works when executing a .sas program. However, my question still persists:

Is there any global setting to change the default formatting for Date and Decimal types?

Is there a way where in any part of my SAS tool if I run a program the default format will be numeric for decimals, and YYYY-MM-DD for DATE, even if I dont include the format statement in my code? 

I want to be able to configure it once and change the default format just like you can change locales or datestyle.

Is there any way to do this?




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 2 in conversation