topic DECIMAL values in Amazon Athena displaying in DOLLAR format in SAS 9.4 by default in New SAS User

DECIMAL values in Amazon Athena displaying in DOLLAR format in SAS 9.4 by default

Test87 — Fri, 22 Sep 2023 16:22:16 GMT

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.

Re: DECIMAL values in Amazon Athena displaying in DOLLAR format in SAS 9.4 by default

Tom — Sat, 23 Sep 2023 00:45:09 GMT

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

Re: DECIMAL values in Amazon Athena displaying in DOLLAR format in SAS 9.4 by default

Test87 — Mon, 25 Sep 2023 09:24:18 GMT

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?