Create snowflake table as select from sql

[DBailey]

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.

[Stu_SAS]

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;

[DBailey]

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.

[SASKiwi]

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

[DBailey]

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

Current version: 9.04.01M8P022223

[SASKiwi]

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.