SAS snowflake pre-assigned library

[freshstarter]

Hello,

I have a pre-assigned sas library for snowflake and we need to use bulkload and bulkunload with the internal stage concept using the pre-assigned library.

All our sas code are pass through code and we dont define any libname statement. In the code , we directly use the pre-assigned library and as far as I read, we cant use bulkload or unload in pass -through.

so thought of using this paramter on sas library on SMC.

Where to define this setting on library? We have other options tab ( under Advanced options ) as well as extended attribute tab ? Can we able to add it over there? I tried few options and it failed.

I need to add BULKLOAD=YES , BULKUNLOAD=YES, BL_INTERNAL_STAGE='@~' , BL_DELETE_DATAFILE=YES,BL_COMPRESS=YES

FYI. we do have a single SAS library for snowflake and it will be used for both load and unload.

Any help is much appreciated. Thanks

[SASKiwi]

There's no reason why you can't use LIBNAMEs with Passthru to Snowflake. We use them. They look like this:

 libname SF snow <Snowflake connection definition>;

  proc sql;
    connect using SF;
    select * from connection to SF
    (SELECT * FROM MySnowFlakeTable);
    disconnect from SF;
  quit;

Also you should be able to add the bulk loading options to the LIBNAME also.

[freshstarter]

Thanks @SASKiwi for your response.
As we are using pre-assigned library, we are not using lib name statement in the code. Below is the code we are using

proc sql;
connect using snow
Create table work.dataset as
select * from connection to snow
(SELECT * FROM Db.Schema.MySnowFlakeTable);
disconnect from snow
quit;

Snow is the preassigned library in smc.the above code is for data unloading.

So that’s the reason I planned to amend the options directly on the smc preassigned library.

There are 1000’s of Sas jobs which we can’t change and include the lib name statement.

Thanks

[carl_sommer]

so thought of using this paramter on sas library on SMC.

Where to define this setting on library? We have other options tab ( under Advanced options ) as well as extended attribute tab ? Can we able to add it over there? I tried few options and it failed.

I need to add BULKLOAD=YES , BULKUNLOAD=YES, BL_INTERNAL_STAGE='@~' , BL_DELETE_DATAFILE=YES,BL_COMPRESS=YES

The correct place to do this in SAS Management Console is as you noted on the library properties -> Options tab -> Advanced Options button -> Other Options tab

Carl Sommer - SAS Technical Support

[freshstarter]

Thanks Carl. Previously I have included under this other option tab but I have separated with semicolon ; , but it throws me an error when using the pre-assigned library.


I have defined like this

Bulkload=yes;BL_Compress=Yes;BL_Internal_Stage=‘@~’;

[SASKiwi]

@freshstarter - Actually we use pre-assigned Snowflake libraries exclusively also. I just used the LIBNAME statement as an illustration 🙂.

[carl_sommer]

You don't want to use the semi-colons.  To test this out, temporarily mark the library as not pre-assigned, set your advanced options, and then display the generated libname statement for the library.  That makes it much more straight-forward to understand how the options are being included.   Once you can generate a libname statement that actually works when submitted, then mark it preassigned once again.

Carl Sommer - SAS Technical Support

[freshstarter]

Thank you.. let me try this out and will keep you updated