topic Re: Create snowflake table as select from sql in SAS Programming

Create snowflake table as select from sql

DBailey — Thu, 13 Aug 2026 13:16:07 GMT

I have an issue creating a table in snowflake using proc sql. I know that behind the scenes, sas is creating a file, putting it to the snowflake stage, and then using snowflake copy to actually load the table. During the copy part, it seems as the column definitions are getting mismatched....where the copy is trying to load a text value into a numeric column. Here's the relevant portion of the log where you can see that it's trying to insert the value 'SOLE OWNER' (which is actually the ACCT_RELAT_NAME) into the IP_ID column (which is numeric but physically just before the ACCT_RELAT_NAME attribute).

NOTE: Get Base Accounts
189 execute by sf (drop table if exists r2_base_accounts);
190 create table snwfk.r2_base_accounts (bulkload=yes) as
191 select
192 e.*
193 ,dhms(date(),0,0,0) as LOAD_DATE format=datetime20.
194 from connection to edw (
195 SELECT
196 AR.BUSINESS_Date
197 ,AR.AR_ID
198 ,AR.ACCT_ID_IN_WH
199 ,ar.BANK_NBR
200 ,ar.BRNCH_NBR
201 ,AR.SRC_SYS_CODE
202 ,AR.AR_TYPE_NAME
203 ,AR.PRIM_OFFICER_CODE

204 ,ar.ALT_OFFICER_CODE
205 ,ar.SAD_OFFICER_CODE
206 ,hp.levl_2_name product_domain
207 ,hp.levl_3_name product_super_class
208 ,hp.levl_4_name product_class
209 ,hp.levl_6_name product_group
210 ,hp.levl_7_name product_sub_group
211 ,AR.HIER_PRODUCT_TYPE_CODE product_id
212 ,hp.leaf_levl_name product_desc
213 ,hp.PRODUCT_PTYPE_CODE ptype
214 ,hp.LOB_CODE product_lob
215 ,AR.BS_SEG_CODE
216 ,AR.COST_CENTER
217 ,ar2ip.ip_id
218 ,ar2ip.acct_relat_name
219 ,ip.ip_id_in_wh
220 ,ip.ip_type_code
221 ,ip.ip_name
222 ,ip.LEGAL_RSDNT_TYPE_name
223 ,ip.hh_id_in_wh HOUSEHOLD_ID
224 ,ip.priv_clnt_code
225 ,ip.priv_clnt_ind
226 ,ip.priv_clnt_trust_code
227 ,ip.prty_cust_type_name
228 ,ar2ar.relat_acct_id_in_wh odp_account
229 ,ar2ar.relat_ar_id odp_ar_id
230 FROM
231 EDWSSL.D_AR AR
232 JOIN edwssl.d_ar_to_ip_relat ar2ip ON
233 ar2ip.ar_id=ar.AR_ID
234 and ar2ip.period_code='D'
235 AND ar2ip.BUSINESS_DATE = &tDBDate
236 JOIN edwssl.d_ip ip ON
237 ip.ip_id = ar2ip.ip_id
238 and ip.period_code='D'
239 AND ip.business_date = &tDBDate
240 JOIN edwssl.d_hier_ar_product hp ON
241 ar.HIER_PRODUCT_TYPE_CODE = hp.LEAF_LEVL_CODE
242 and hp.period_code='D'
243 AND hp.business_date = &tDBDate
244 LEFT OUTER JOIN edwssl.D_AR_TO_AR_RELAT ar2ar ON
245 ar2ar.BUSINESS_DATE = &tDBDate
246 and ar.ar_id = ar2ar.ar_id
247 AND ar2ar.PERIOD_CODE ='D'
248 AND ar2ar.relat_code = 'HAS ODP FROM'
249 WHERE
250 AR.BUSINESS_DATE = &tDBDate
251 AND AR.PERIOD_CODE ='D'
252 and ar.ar_type_name !='APPLCTN'
253 and ar.ar_type_name !='FEATURE'
254 ) e;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error executing COPY command: Numeric value 'SOLE OWNER' is not recognized File
'SASSNBL_6C042481-B5CC-D549-ABC8-EB31972007F2-01.dat', line 3206396, character 231 Row 3206396, column
"R2_BASE_ACCOUNTS"["IP_ID":22] If you would like to continue loading when an error is encountered, use other values such
as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info
loading_data' in a SQL client.

Re: Create snowflake table as select from sql

Stu_SAS — Thu, 13 Aug 2026 16:48:44 GMT

Hey @DBailey! Can you add these lines to your program before PROC SQL and post the results? (Just make sure there's nothing sensitive first)

options sastrace=',,,d' sastraceloc=saslog;

Re: Create snowflake table as select from sql

DBailey — Thu, 13 Aug 2026 18:08:11 GMT

not much there

33
34 options stimer;
35 options sastrace=',,,d' sastraceloc=saslog;

56
57 PROC SQL NOPRINT;
58 connect using edwusr as edw;
59 CONNECT USING SNWFK AS sf;
60 connect using retailh as d;
61

140 execute by sf (drop table if exists r2_base_accounts);
141 create table snwfk.r2_base_accounts (bulkload=yes) as
142 select * from work.r2_base_accounts;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error executing COPY command: Numeric value 'SOLE OWNER' is not recognized File
'SASSNBL_817DD106-4724-B94A-B56A-A6400B577F13-00.dat', line 3511769, character 231 Row 3511769, column
"R2_BASE_ACCOUNTS"["IP_ID":22] If you would like to continue loading when an error is encountered, use other values such
as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info
loading_data' in a SQL client.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Re: Create snowflake table as select from sql

SASKiwi — Thu, 13 Aug 2026 20:04:53 GMT

What happens if you don't use the BULKLOAD option? Still the same errors? Also what version and maintenance level of SAS is this?

Re: Create snowflake table as select from sql

DBailey — Thu, 13 Aug 2026 20:23:24 GMT

it works...just painfully slow loading 7MM records.

Current version: 9.04.01M8P022223

Re: Create snowflake table as select from sql

SASKiwi — Thu, 13 Aug 2026 20:43:09 GMT

I'm wondering if there's some unprintable characters in the load data upsetting the layout of bulk load file. Maybe try bulk loading a small sample first.

Re: Create snowflake table as select from sql

Tom — Sat, 15 Aug 2026 01:30:46 GMT

That is a strange error. It seems to be saying it tried to read the text string 'SOLE OWNER' into a numeric variable.

Sounds like something is causing the parsing of the bulk loaded file to fail.

Do you know where in your data you have that value? If it only appears only a few times you might be able figure out what values are causing the trouble by looking at the values of a couple of observations before where it first appears.

You might have unbalanced quotes in your data fields and SAS and Snowflake don't agree on how to handle them. Or perhaps your data includes values that look like the delimiter that bulkload is using to parse the records. Or perhaps they don't agree on the encoding that is being used?

Re: Create snowflake table as select from sql

DBailey — Sun, 16 Aug 2026 12:57:51 GMT

SOLE OWNER appears a lot as it describes the relationship between customer and account.

The source is an oracle database. There might be quotes inside the text columns, but it's not something I could change even it it's there (say customer say is O'BRIAN). It might be a delimiter problem...but I don't see any options on telling sas what delimiter to use in the bulk load process.

Re: Create snowflake table as select from sql

Tom — Sun, 16 Aug 2026 14:31:09 GMT

If the goal is to move data between two different foreign databases then you should get better results if you switch to PROC FEDSQL instead.

Re: Create snowflake table as select from sql

DBailey — Mon, 17 Aug 2026 20:00:11 GMT

I did try that...same issue