How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via ODBC?

[aabje]

Hi,

We are reading data from Snowflake to SAS EG via ODBC.

Seems like default date format is being set to DATE9 no matter what the date format is in Snowflake.

How can we change so the format is DDMMYY10 instead?

If we set STRINGDATES=YES in the LIBNAME statement the date format changes to VARCHAR.

Was hoping there would be a setting like SASDATEFMT=DDMMYY10 (or DBSASTYPE) in the LIBNAME Statement but I haven't found such setting that works.

I also found this:
Solved: Change DATE formats to DDMMYYS10. for ALL (unknown number) date variab... - SAS Support Comm...

And had the idea to run the script in autoexec but it seems way too slow and would add alot of load time upon initial test (correct me if I'm wrong here).

Are there maybe some alternative ODBC drivers for snowflake or some proxy middleware that can convert the default date data type?

[PaigeMiller]

I guess the idea of changing the database extract format default seems overly complicated and unnecessary to me.

Once you create the SAS data set (with DATE9 formats) via SQL, you can change the format then. Assumes your data set is WORK.EXTRACT.

proc datasets library=work;
      modify extract;
      format date1 date2 date3 ddmmyy10.;
run;

[aabje]

Thanks for the reply.

However we would like to avoid any extra processing/scripting to change the format for each column that would need to be done every time we open SAS EG to achieve the result.

The data is in Snowflake with quite a lot of tables and columns and tables, we are not copying it just once to a SAS dataset and then using it.

Our SAS Library is successfully connected to the Snowflake schema via ODBC so we can read/browse/query all data without copying it to a SAS dataset first.

The problem is the ODBC connection seem to force all date columns to DATE9 format, no matter the date type in original data.

[LinusH]

I suggest that you write a product suggestion.
https://communities.sas.com/t5/SAS-Product-Suggestions/idb-p/product-suggestions
You'll have my vote.

[aabje]

LinusH sounds like you are familiar with the issue. Did you conclude there is no easy solution? What workarounds did you explore?

Did you try this also
https://communities.sas.com/t5/SAS-Procedures/Change-DATE-formats-to-DDMMYYS10-for-ALL-unknown-numbe...

?

Is this really something hardcoded in SAS Eg or is it something enforced by the Snowflake ODBC driver?

Are there perhaps more custom or open source ODBC drviers (like this https://www.cdata.com/kb/tech/snowflake-odbc-sas.rst ) which could give the ability for custom date type foirmat?

[ChrisHemedinger]

Are you using a libname with SAS/ACCESS to ODBC for this connection? If so, then it really has nothing to do with EG specifically. SAS/ACCESS to Snowflake might provide more flexibility.

[aabje]

Yes I believe we use the libname odbc method.

In your linked documentation it states the default SAS formats for the Snowflake data types where it says DATE type default is DATE9
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0qnx5bs1vcs3in0zazami3levju.htm

Any idea how it can provide more flexibility as you suggest, and let us adjust this setting or work around it?

[Tom]

Sounds like you want to automate something. So describe in more detail what you want to automate so we can see where you can place the solution you already demonstrated.

For example if the goal is to make it easy for users to copy a table from the Snowflake database into a SAS dataset then you might create a macro they can use.  Then that macro can apply the logic you identified to find the DATE variables and apply the desired formats.  It might also allow you to add some logic to optimize the transfer in other ways

NOTE: Is there some reason why you want to convert from a non-confusing display format to one that will confuse half of your audience?

[aabje]

Why make this assumption about my "audience"? What exactly is confusing to you about the DDMMYY format which is widely used in many parts of the world?

In this case the users are already using the DDMMYY10 format in the other SAS Libraries.

Only in the library which is connected to Snowflake the format is different.

We are not trying to copy anything from snowflake to SAS dataset.

We use SAS Eg directly on the Snowflake data with the Snowflake odbc libname method. I know this is not super common setup, and usually a temporary solution during migration of platforms. However we are far from the only ones having used this method so hoping to find others who experienced similar issue and found some solution or workaround

[Tom]

You should open a ticket with SAS Support to see if they have any suggestions.  The documentation for SAS/ACCESS to ODBC seems pretty clear that it will use DATE format with SQL_DATE values.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n13gtugcxgdqstn1pooivt622n4q.htm

If you have a lot of users pointing to a fixed number of SNOWFLAKE schemas perhaps it will be worth your while to create a library with SAS PROC SQL views that point to the SNOWFLAKE tables and let the users access the data that way.  

libname snowsas 'some path';
proc sql;
create view snowsas.table1 as 
  select var1
       , var2 format=ddmmyy10.
  from snow.table1
  using libname snow odbc .....
;
quit;

You could automate the creation of the SQL views.  I have no idea whether it would have any impact on performance so you should test it before spending too much effort on it. In particular check performance of any joins you might be doing between datasets (aka tables) in the same SNOWFLAKE schema. You could even add LABELS to your variables to make them more user friendly.  Unfortunately with PROC SQL syntax there is no way to remove the unneeded $xx. formats that SAS/ACCESS insists on attaching to character variables.

Note that in US it is common to use MDY order for date numeric strings.  I tell those users the same thing. Why use a display format that is potentially confusing?

[Kurt_Bremser]

There is a not so small part of the world where 05-06-2024 will be read as 2024-05-06.

YMD is non-confusing everywhere, and an international standard (ISO 8601).

[ballardw]

---

@aabje wrote:
What exactly is confusing to you about the DDMMYY format which is widely used in many parts of the world?

---

IF a value is presented as you say, DDMMYY or for example 01-02-03

it could represent:

1 Feb 1903 or 1 Feb 2003

2 Jan 1903 or 2 Jan 2003

3 Feb 1901 or 3 Feb 2001

Even with a 4-digit year the possibility of the 1 Feb / 2 Jan interpretation exists.

If you have an organization standard not as much of a problem. But it should be stated that it is an organization standard...

[SASKiwi]

@aabje - I think the SASDATEFMT dataset option will help here. You can't apply it as a LIBNAME option, but you can on any dataset created from Snowflake data.

libname MySnow Snow <Connection options>;
data MyDate (sasdatefmt=(dates='ddmmyy10.'));
  set MySnow.MyTable;
run;

[aabje]

Thanks for the input!

We are aware that there are various ways we can use the existing datasets as source and create new datasets/tables/views with the right data type format for the dates.

However we would like to avoid any extra processing/scripting to change the format for each column that would need to be done every time we open SAS EG to achieve the result, or a new library with views of the actual library, that would need to be maintained whenever something changes in the original library.

The data is in Snowflake with quite a lot of tables and columns and tables, we are not copying it just once to a SAS dataset and then using it. Data is alive in snowflake and changes, and we would like to handle all possible date columns the same.

Your replies make me think that this is not possible with some setting in SAS Eg similar to the STRINGDATES=YES (which changes dateformat from DATE9 to varchar) setting and if so I will of course accept it and try to work around it.

However I am still looking for feedback on my idea about using some custom or open source ODBC driver or proxy if it is the ODBC driver and not SAS Eg that force the format to DATE9.  But maybe there is some other more suitable subforum for this question?

[Kurt_Bremser]

Create a SAS library containing views to the Snowflake tables, where you make all the necessary adjustments. Your users will then use the views.