topic Re: accessing snowflake tables in sas code having issue with case sensitivity in SAS Programming

accessing snowflake tables in sas code having issue with case sensitivity

Shanthi123 — Tue, 10 Sep 2024 12:20:39 GMT

can someone help on, this.

i have a sas reporting code which is accessing snowflake ,oracle, greenplum etx multiple source tables. as we all know sas doesnot have issue with case sensitivity, but when i use snowflake in lower case it is throwing an error data doesnot exist. but when i use the same snowflake table in uppercase its giving output. can someone help on this ?

Re: accessing snowflake tables in sas code having issue with case sensitivity

ballardw — Tue, 10 Sep 2024 17:22:46 GMT

Are you attempting to write code that is intended to work against multiple data sources without modification? Or is this "I have a Snowflake version of a report and it has this problem?"

It would help us to provide a relevant targeted solution if you could share your code. Best would be to include a Log entry with the errors and without. An example of how your code connects to Snowflake would be helpful as someone may know a connection setting to "ignore" case for some operations.

I have to guess that this might involve where the name of a table or variable is used. If the source, such as Snowflake, is sensitive to case for tables or variables (SAS isn't) then you have to know the case of the spelling. There may be workarounds possible but would have to see examples. And some of the workarounds may impose notable performance hits.

Re: accessing snowflake tables in sas code having issue with case sensitivity

Tom — Tue, 10 Sep 2024 17:46:13 GMT

Snowflake is a variation of Posgresl.

When you define the datasets ("tables") and variables ("columns") then make sure you define them using only lowercase letters. If you reference them normally (select Varname from DSname) then it will look for lowercase variables. If you use quoted strings (select "Varname" from "DSname") then it will look for names that exactly match the case used. If you define them with mixed case (aka "CaMeL" case) then you will need to always reference them using quoted strings in your SQL code.

https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive

Re: accessing snowflake tables in sas code having issue with case sensitivity

Shanthi123 — Wed, 11 Sep 2024 06:32:32 GMT

here is my code :

libname rodm snow
   server="XXXXXXXXXX.snowflakecomputing.com"
      db=XXXXXX warehouse=XXXXX
      role=XXXXXX
      schema=XXXXXXX
user=
password=;
proc sql;
select count(*) from rodm.vh_assessment_data;
quit;
* when i try to run  above code its throwing an error : data doesnot exist.
proc sql;
select count(*) from rodm.VH_ASSESSMENT_DATA;
quit;
* but when i try to run the above code it is giving output with some count.

Re: accessing snowflake tables in sas code having issue with case sensitivity

SASKiwi — Thu, 12 Sep 2024 02:40:18 GMT

I suggest you rerun your tests adding the - PRESERVE_TAB_NAMES = NO - option to your LIBNAME to see if that changes the behaviour.

Re: accessing snowflake tables in sas code having issue with case sensitivity

Tom — Thu, 12 Sep 2024 13:30:11 GMT

First turn on the options that tell SAS to display the implicitly created database code in the SAS log.

options nostsuffix sastrace=',,,ds' sastraceloc=saslog ;

And then run your examples and compare the differences in the generated SQL code that SAS ran.

Second try writing the SQL code yourself using passthru SQL. You can use your existing LIBREF to make the connection, but you will have to include the schema in your SQL code.

libname rodm snow ... schema=MYSCHEMA ... ;
proc sql;
connect using  rodm;
select * from connection to rodm
(select count(*) from MYSCHEMA.vh_assessment_data)
;
select * from connection to rodm
(select count(*) from MYSCHEMA."vh_assessment_data")
;
select * from connection to rodm
(select count(*) from MYSCHEMA.VH_ASSESSMENT_DATA)
;
select * from connection to rodm
(select count(*) from MYSCHEMA."VH_ASSESSMENT_DATA")
;
quit;

Third figure out what the SNOWFLAKE method is do listing the tables/views in a schema and run that using pass thru SQL and check the case of the names for the tables.

Re: accessing snowflake tables in sas code having issue with case sensitivity

NewSASPerson — Tue, 24 Sep 2024 15:42:27 GMT

I was having the same issue. This worked for me. Thank you!