SAS Programming

DATA Step, Macro, Functions and more
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-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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