Hi Team,
I am not able to access one particular table in snowflake due to the length of the table name is more than 32 byte.
I am connecting to Snowflake using libname; could you help me to give some idea on reading this table.
%let sf_driver=**********************;
%let sf_account=********************;
%let sf_server=*********************************;
%let sf_database=********************;
%let sf_schema=***************************;
%let sf_stage=***************;
%let sf_uid=*******************;
%let sf_pwd=**************************;
/* libnames for implicit connections */
libname snow ODBC complete="driver=&sf_driver;uid=&sf_uid;pwd=&sf_pwd;server=&sf_server;database=&sf_database"
schema=&sf_schema
autocommit=no
insertbuff=32767
readbuff=32767
preserve_tab_names=yes;
data _1;
set snow.table_name_where_length_is_more_than_32_byte;
run;
This has been answered before. After first berating the idiot that thinks it a reasonable way to run a computer system to use object names that are so long you just need to switch to using pass thru SQL to access the datasets ("tables") and variables ("columns") with the ridiculous names.
proc sql;
connect using snow;
create table _1 as select * from connection to snow
(select name1
, name2
, variable_name_where_length_is_more_than_32_bytes as name3
from table_name_where_length_is_more_than_32_bytes
)
;
quit;
If you have a working libref, you can take a shortcut using "USING":
CONNECT USING libref <AS alias>;
No need to re-connect to the database if you already have a libref defined that connects to the database. The CONNECT USING syntax allows you to connect using an existing libref. That way you don't have to repeat your credentials again. And also you reduce the load on the database by opening only one connection instead of two.
Your code used SNOW as the libref for the connection you made to the database that I why I used that name in my code.
Pattern:
libname SNOW ......;
proc sql;
connect using SNOW ;
select * from connection to SNOW (...);
execute by SNOW (...);
quit;
You probably need to include the schema name when referencing the table in the pass thru sql.
Perhaps &sf_schema..table_name ?
Test the passthru SQL syntax using some other way to run SQL code in Snowflake. Such as the browser based tool that Snowflake has.
The other thing to check is the name of the variables in the snowflake dataset. Using SELECT * you are trying to retrieve them as is. If any of those are also not valid SAS names you will have problems. But I would expect a different error message.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.