08-19-2024
aabje
Fluorite | Level 6
Member since
08-15-2024
- 7 Posts
- 0 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by aabje
Subject Views Posted 246 08-16-2024 04:17 AM 2338 08-16-2024 02:55 AM 2464 08-15-2024 09:53 AM 2473 08-15-2024 09:44 AM 2549 08-15-2024 07:17 AM 2551 08-15-2024 07:12 AM 2605 08-15-2024 04:57 AM -
Activity Feed for aabje
- Posted Re: How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via on SAS Programming. 08-16-2024 04:17 AM
- Posted Re: How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via on SAS Programming. 08-16-2024 02:55 AM
- Posted Re: How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via on SAS Programming. 08-15-2024 09:53 AM
- Posted Re: How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via on SAS Programming. 08-15-2024 09:44 AM
- Posted Re: How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via on SAS Programming. 08-15-2024 07:17 AM
- Posted Re: How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via on SAS Programming. 08-15-2024 07:12 AM
- Posted How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via ODBC? on SAS Programming. 08-15-2024 04:57 AM
- Tagged How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via ODBC? on SAS Programming. 08-15-2024 04:57 AM
- Tagged How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via ODBC? on SAS Programming. 08-15-2024 04:57 AM
- Tagged How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via ODBC? on SAS Programming. 08-15-2024 04:57 AM
- Tagged How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via ODBC? on SAS Programming. 08-15-2024 04:57 AM
- Tagged How change default ODBC date format (DATE9->DDMMYY10) when connecting SAS EG to Snowflake via ODBC? on SAS Programming. 08-15-2024 04:57 AM
08-16-2024
04:17 AM
From my last post: "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." This is not the solution we are looking for, as to my understanding it requires the library of the views to be maintained and updated whenever the original Snowflake library changes. Even if it can be somewhat scripted and automated it is still not optimal. Open to other suggestions or feedback on my idea about custom ODBC driver or middleware proxy.
... View more
08-16-2024
02:55 AM
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?
... View more
08-15-2024
09:53 AM
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
... View more
08-15-2024
09:44 AM
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?
... View more
08-15-2024
07:17 AM
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-number-date/td-p/366637 ? 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?
... View more
08-15-2024
07:12 AM
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.
... View more
08-15-2024
04:57 AM
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 Communities 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?
... View more