SAS Support Communities

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

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

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

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

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

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