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.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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