DBailey Tracker

Re: Create snowflake table as select from sql

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

I did try that...same issue

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

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

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.

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: SAS Logs from LSF Scheduled jobs

DBailey — Tue, 18 Mar 2025 20:44:21 GMT

This is what I did (which works in testing...not sure about production yet). Based on what sas thinks the log is, we looked at that location for the files matching that description and took the one with the most recent timestamp.

%macro rma_error_alert();

%put RMA_ERROR_ALERT;
%put SYSCC=&SYSCC;


%if &SYSCC Gt 4 %then %do;
	
	
	%PUT _ALL_;

	%LET tLOG=%SYSFUNC(GETOPTION(LOG));
	%LET tWork=%SYSFUNC(GETOPTION(Work));	
	%let ActLog=X;

	%put tlog=&tlog;
	%put twork=&twork;
	%put SYSCC=&SYSCC;
	
	filename oscmd pipe "ls -1tr &tLog.*.*|tail -1";

	data _null_;
		infile oscmd truncover;
		input filename $100.;
		call symput('ActLog',filename);
		run;


	%put ActLog=&ActLog;

	filename tmail email	
		from=('xxxxx')
		SENDER=('xxxxx')
		to=( 'xxxxxxx')
		subject=('Production Error')
		%if "&ActLog" ne "X" %then %do;
			attach = ("&ActLog" ct="text/plain")
			%end;
		;
	
	data _null_;
		file tmail;		
		current= "SYSCC=&SYSCC";
		LogPath = "Log Path: &tLog.";
		Workpath = "Work Path: &tWork.";
		SEText = "SysErrorText: &SYSERRORTEXT";
		put "SYSCC=&SYSCC.";
		put "Logpath=&tLOG.";
		put "Workpath=&tWork.";
		put "SysError: &SYSERRORTEXT.";
		put ;
		put ;
		put "This email address is not monitored for replies.";
		put ;
	run;
	
	%end;

%mend;

Re: SAS Logs from LSF Scheduled jobs

DBailey — Tue, 18 Mar 2025 19:01:25 GMT

which seems to match our config

Is there a way to map it back so that the executing job knows the actual log name?

Re: SAS Logs from LSF Scheduled jobs

DBailey — Tue, 18 Mar 2025 17:19:13 GMT

We use the LSF Scheduler to submit jobs. I tested a few ways...and the most recent is that (from management console) the "command to run" is

/sasconfig/compute/Lev1/SASApp/BatchServer/sasbatch.sh -log /mkt/Logs/RMA/Z_Test_z_test_con.log -batch -noterminal -logparm "rollover=session" -sysin /mkt/BatchJobs/z_test_con.sas

what got actually created was

/mkt/Logs/RMA/Z_Test_z_test_con.log_2025.03.18_12.16.34.log

for the test, we have a macro that is included in z_test_con.sas that checks the syscc value. If it is >4, then it sends an email alerting of a failure (using sas email processing). I need to attach the lob to that email...but can't work out the actual log file name.

Re: SAS Logs from LSF Scheduled jobs

DBailey — Tue, 18 Mar 2025 16:28:20 GMT

yes.

SAS Logs from LSF Scheduled jobs

DBailey — Tue, 18 Mar 2025 13:55:53 GMT

I'm trying to map the log file that SAS thinks it's using to what LSF actually uses so that we can email the log if the job fails. In testing, I see this from SAS's perspective:

19 %LET LOG=%SYSFUNC(GETOPTION(LOG));
20 %put log=&log;
log=/mkt/Logs/RMA/Z_Test_z_test_con_#Y.#m.#d_#H.#M.#s.log

from the actual log is /mkt/Logs/RMA/Z_Test_z_test_con_#Y.#m.#d_#H.#M.#s.log_2025.03.18_08.51.10.log

How can I find the actual log?

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 20:29:03 GMT

specifying a schema is required if you need something other than the default and if you want to browse the library contents. I think the most often used default is dbo

and i'm going to try and get the auth domain so I can execute the libname statement directly.

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 20:26:50 GMT

I just tried that:

proc sql;

select count(*) as recs

from quest.businessunit;

connect using quest as q;

create table work.test as select * from connection to q (

select * from businessunit

);

disconnect from q;

quit;

the count worked but the connect using did not.

ODBC_2: Prepared: on connection 1
select COUNT(*) as "recs" from "dbo"."businessunit" TXT_1 WITH (NOLOCK)

ODBC_3: Executed: on connection 1
Prepared statement ODBC_2

ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.
37
38 connect using quest as q;
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

I'll see if I can find the authdomain and try a "connect to" instead of "connect as"

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 19:36:16 GMT

we do use "connect using" for other predefined libraries...but they're oracle and not sqlserver. just trying to troubleshoot a bit

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 19:15:18 GMT

I see

credentials cache 'XXXXX' not found

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 19:06:06 GMT

So in our environment, the username/passwords are stored in an authdomain and are used in the predefined library. If I try and use just the datasource name, I get this:

proc sql noprint;
35 connect to sqlsvr as q (datasrc=QUEST);
ERROR: CLI error trying to establish connection: [SAS][ODBC SQL Server Wire Protocol driver][SQL Server]Login failed for user ''.

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 18:59:34 GMT

I do.

proc sql and connect using

DBailey — Tue, 14 Jan 2025 14:14:20 GMT

We have a sas grid environment with predefined libraries (against sql server). This works:

proc sql noprint;
create table work.test as select * from quest.BusinessUnit;
quit;

However, this fails:

proc sql noprint;
connect using quest as q;
create table work.test as
select * from connection to q (
	select * from businessunit
);
disconnect from q;
quit;

with this error:

39 connect using quest as q;
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

Any suggestions?

Enterprise Guide 8.2 Pane options

DBailey — Wed, 25 Mar 2020 00:02:43 GMT

Trying the latest EG....I can't figure out how to have the task panes hidden but docked like we did in 7.1. The options are Float and Hide....when I hide, I have to go back to the menu to display it again. 7.1 could dock but only pop out when you hovered.

Re: Range selection based of lowest price

DBailey — Fri, 15 Mar 2019 16:00:30 GMT

I don't understand what you're asking me to do.

Re: Range selection based of lowest price

DBailey — Fri, 15 Mar 2019 14:37:17 GMT

Couldn't follow much of the discussion....but this is a simple coding exercise for proc sql. It may take a while to run on 300GB of data...but you would have to process that data anyway to get the results you want.

Proc sql noprint;
create table want as
select
product,
on_sale_date,
off_sale_date,
min(Price) as Price
from have
group by
product,
on_sale_date,
off_sale_date
;
quit;