BookmarkSubscribeRSS Feed
bitcruncher
Obsidian | Level 7

Not a question rather an outcome of some weird errors with Snowflake.

 

Created a Snowflake table using passthrough and CREATE TEMP TABLE. table name was myTable

 

then PROC APPEND BASE=<<libname>>.mytable data=....

 

We were getting misformed Snowflake SQL in the statements generated by SAS in PROC APPEND. The object was to use that proc to populate the temp Snowflake table.

 

Spoke to DBA, he pointed out that case matters to Snowflake, and SAS passes through what you give it.

 

Snowflake had not found mytable in the SQL that SAS passes through as the create used myTable.

 

Changed every instance of the libname, database name and anything else Snowflake to UPPER CASE and it all worked fine.

 

I had already opened a track with SAS and they confirmed this. 

 

BACK TO COBOL CODING 🙂 !

1 REPLY 1
ballardw
Super User

@bitcruncher wrote:

Not a question rather an outcome of some weird errors with Snowflake.

 

Created a Snowflake table using passthrough and CREATE TEMP TABLE. table name was myTable

 

then PROC APPEND BASE=<<libname>>.mytable data=....

 

We were getting misformed Snowflake SQL in the statements generated by SAS in PROC APPEND. The object was to use that proc to populate the temp Snowflake table.

 

Spoke to DBA, he pointed out that case matters to Snowflake, and SAS passes through what you give it.

 

Snowflake had not found mytable in the SQL that SAS passes through as the create used myTable.

 

Changed every instance of the libname, database name and anything else Snowflake to UPPER CASE and it all worked fine.

 

I had already opened a track with SAS and they confirmed this. 

 

BACK TO COBOL CODING 🙂 !


Yet another example of know thy system (or programming language). SAS is pretty casual about some things, at least until doing actual value comparisons, and case of variable names, dataset names, library names etc are flexible in the code. BUT go into the dictionary tables holding the metatdata and you find SAS references Libname and Memname in upper case. So if you search the metadata tables with mixed or lower case you won't find those libraries or data sets (memname) values. So I'm not surprised a DB like Snowflake has that sort of limit.

 

I dont' remember which language I was exposed that had something like this:

a= 3;

A= a+5;

And that referenced two variables, a and A. Talk case sensitivity.

 

And if we're going back to COBOL bring back the punch card or tapes. That way we have non-electronic backups of the code...

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 147 views
  • 4 likes
  • 2 in conversation