topic Re: Snowflake Queries in SAS Enterprise Guide

Snowflake Queries

SASMom2 — Sun, 03 May 2026 03:15:14 GMT

I need some help figuring out if what I want to accomplish is possible. I pull base data from our system/databases in Snowflake using sql queries. The result tables are saved in Snowflake. Then I pull data (saved in Snowflake tables) into SAS EG by connecting to Snowflake through libnames. Then I analyze data in SAS EG and create a final report. For some of our reports there is a lot of back and forth between Snowflake cloud (Snowflake app, not in SAS EG) and SAS EG.

Is there anyway, I can create a procedure/macro (or something) in snowflake for each sql query that is in Snowflake cloud and call that in SAS EG? This way, I do not have to go back and forth between Snowflake and SAS EG.

Thank you in advance!

Re: Snowflake Queries

Tom — Sun, 03 May 2026 19:11:58 GMT

You can use SAS code to execute queries in Snowflake. If you already have a LIBREF defined pointing to Snowflake database then that should be all you need.

You can use PROC SQL to run queries. First make the connection by using the CONNECT USING statement. Then you can use the EXECUTE statement to run a command that does not return any output. Or the FROM CONNECTION TO clause for one that does.

So let's assume you defined the libref SNOW pointing to Snowflake

proc sql;
connect using snow;
execute by snow (...some Snowflake command...);
create table want as 
select * from connection to snow
(... some Snowflake query ...)
;
quit;

You can use the front end tool Enterprise Guide to create and run the SAS code.

Re: Snowflake Queries

SASMom2 — Mon, 04 May 2026 01:34:33 GMT

Thank you so much for your reply. I feel like I am almost there but yet! Below is my code.

--------------------- Code -----------------------------

proc sql;

connect using snow as sf;

execute by sf;

create table test as

select * from connection to sf

(select distinct

b.cust_seg_nbr

,b.cust_seg_nm

from snowflake_db as b

where b.cust_seg_nbr in ('91090334', '091090334', '0091090334', '00091090334')

);

quit;

-----------------------------------------------------

The "snowflake_db" is the location of a table in a database in Snowflake.

I am getting below error when I run it. I do not know the reason I am getting it.

------------ below is the error I am getting -------------------------

79
76
ERROR 79-322: Expecting a (.

ERROR 76-322: Syntax error, statement will be ignored.

---------------------------------------------------------------------

Thank you

Re: Snowflake Queries

Tom — Mon, 04 May 2026 02:15:13 GMT

Remove the partial EXECUTE statement, that is what is missing the ().

It does not look like you are trying to execute anything so you don't need that statement.

Note that everything inside the () after the FROM CONNECTION TO xx clause has to be valid Snowflake SQL syntax.

Re: Snowflake Queries

SASMom2 — Mon, 04 May 2026 03:12:17 GMT

That worked great! Thank you!

Now, I am going to try a bit complicated query. This query filters records using member first name, last name and date of birth under 'where' section in proc sql. In Snowflake names and date of birth are set as below on the top of the query

---------------------------------------------

SET fname = 'ABC';
SET lname = 'DEFG';
SET BMTH= '9999-12-31';

--------------------------------------------------------

where

first_name=$fname

and last_name=$lname

and DOB = $BMTH;

------------------------------------------------------------------------------------------

Where and how in SAS should I put the 'SET' statements?

Thank you

Re: Snowflake Queries

Tom — Mon, 04 May 2026 04:25:21 GMT

Why? Are you trying to do code generation?

You can use SAS's macro language to do code generation.

%let fname = 'ABC';
%let lname = 'DEFG';
%let BMTH= '9999-12-31';
....
proc sql ;
....
where first_name=&fname
  and last_name=&lname
  and DOB = &BMTH
....

Re: Snowflake Queries

SASMom2 — Mon, 04 May 2026 17:03:15 GMT

Thank you for all your help! When I tried to run a Snowflake query that pull data from one table, it ran quickly but when I tried q query that pulls data from 5 tables in Snowflake, it kept running. Finally, I stopped it after 5 hours. When I run the same query directly in Snowflake, it runs in under a minute. So, I would definitely be using your method for queries that use one table but not for queries that use multiple tables. I was hoping that it would work for complicated queries as it would save a lot of manually going back and forth between Snowflake and SAS.

Thank you!

Re: Snowflake Queries

Tom — Mon, 04 May 2026 20:16:09 GMT

@SASMom2 wrote:

Thank you for all your help! When I tried to run a Snowflake query that pull data from one table, it ran quickly but when I tried q query that pulls data from 5 tables in Snowflake, it kept running. Finally, I stopped it after 5 hours. When I run the same query directly in Snowflake, it runs in under a minute. So, I would definitely be using your method for queries that use one table but not for queries that use multiple tables. I was hoping that it would work for complicated queries as it would save a lot of manually going back and forth between Snowflake and SAS.

Thank you!

Without an example I can only guess what you did. But here are some possibilities:

You somehow asked SAS to execute the joins instead of giving the whole query to Snowflake to run. If you ended up doing that then SAS would probably try to pull the whole table over and then join them which could take a very long time. For example do NOT do something like this:

select * from work.mytable a left join snow.dbtable b 
  on a.id = b.id
;

Instead either upload the list SAS dataset into Snowflake then then perform the join completely in Snowflake.

The sizes of the result sets was vastly different between the two queries and so you are seeing how slow your connection to Snowflake is.

There are options you can turn on to have SAS write the SQL it sends to Snowflake into the SAS log so you can see exactly what SAS ended up asking to be run.

https://documentation.sas.com/doc/en/workbenchcdc/v_001/vwbacdata/n0sa0yux9sqf90n1ttzxhko2cit4.htm

You can then try running that code in Snowflake and time how long it takes.

Re: Snowflake Queries

LinusH — Tue, 05 May 2026 13:08:50 GMT

Please provide the query (and log) so we can help analyze it, ideally with using the options in the link from @Tom.

Re: Snowflake Queries

SASMom2 — Tue, 05 May 2026 15:27:38 GMT

I did exactly what you said not to do. Can you please clarify below.

"Instead either upload the list SAS dataset into Snowflake then then perform the join completely in Snowflake."

It is from your message.

Thank you

Re: Snowflake Queries

SASMom2 — Tue, 05 May 2026 16:41:35 GMT

Below is my query. Please note that I have changed the names of fields/tables/schema/db, They are all made up names but the logic and layout is same. When I run it, it does not go past the %include statement. It keeps running.. there is no log after that. Hope it helps. Thank you!

------------------------- query --------------------------------------------------------------------

%include "Snowflake_libname.sas"; (has libnames)

PROC SQL;

CONNECT USING SNOW AS SF;

CREATE TABLE test AS

select * from connection to SF

(select DISTINCT

b.cust_nbr

,b.cust_nm

,cast((a.PT_KEY) as varchar(20)) as PT_key

,c.indvid

,c.first_name

,c.last_name

,c.clmnt_dob

,a.dx_code

,d.dx_desc

,a.px_cd

,a.service_dt

,a.process_dt

,a.PD_DT

,e.location

,a.cov_paymt

,a.net_pd_paymt

from "db"."schema"."table1" a

join "db"."schema"."table2" as b

on a.PT_KEY = b.PT_KEY

join "db"."schema"."table3" c

on a.PT_KEY = c.PT_KEY

left join "db"."schema"."table4" d

on a.dx_cd = d.dx_cd

left join "db"."schema"."table5" e

on a.location_cd = e.location_cd

where

indvid = 1234567

and c.first_name='Jane'

and c.last_name='Doe'

and a.service_dt >= '2024-04-01');

quit;

Re: Snowflake Queries

Tom — Thu, 07 May 2026 02:43:05 GMT

How many observations does that query return? You can test it in Snowflake or just wrap it into something like:

....
select * from connection to sf
(select count(*) as nobs from
 (... the query goes here ...) a
);

If you want to move a LOT of data then you might get better results using Snowflakes ability to generate a CSV file into some place like Amazon S3 bucket that you could then read with SAS code to make a dataset.

Are any of those variables defined as STRING in Snowflake? SAS datasets only have two types of variables. Fixed length character strings and floating point numbers. So when you have a character variable of unknown length in your Snowflake table SAS will use some default length for the variable when making the dataset. It might pick something very long such as SAS's maximum length of 32,767 bytes. That would make the processes of creating the SAS dataset take a very long time even if the transfer from Snowflake ran quickly.

Try just pulling over a handful of the observations (Snowflake's version of SQL should support something like a LIMIT clause to trim the number of observations returned by the query). You can then run PROC CONTENTS on the resulting SAS dataset and see how the variables are defined.