DBailey Tracker

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;

Re: Reset OBS= Global Option

DBailey — Wed, 02 Jan 2019 19:11:23 GMT

isn't it just obs=max?

Re: Upadting row value in sas dataset

DBailey — Tue, 11 Dec 2018 21:49:46 GMT

Sounds pretty straight-forward....

proc sql;

update work.have

set udf_value = '2016q4'

where udf_nm='X_CASE_PRD' ;

quit;

Re: Upadting row value in sas dataset

DBailey — Tue, 11 Dec 2018 19:59:18 GMT

I could not understand what you wanted to do.

Typically, you can update datasets via proc sql:

If you want to update the new column to a null value if it can't find a match, then you might use something like this:

proc sql;

update work.have as t1

set t1.udf_value=(select new_value from some_data_set where col=t1.col)

where t1.udf_nm='X_CASE_PRD' ;

quit;

If you want to update the new column only when there is a match, you can add to the criteria:

proc sql;

update work.have as t1

set t1.udf_value=(select new_value from some_data_set where col=t1.col)

where t1.udf_nm='X_CASE_PRD' and exists (select new_value from some_data_set where col=t1.col);

quit;

Re: Count for a specific date range

DBailey — Wed, 07 Nov 2018 18:43:22 GMT

Re: Importing files in SAS and storing them in Oracle as blobs

DBailey — Mon, 29 Oct 2018 16:09:47 GMT

you can definitely store the row/colum data from the excel sheet in oracle as described....but is that what you want to do? It sounded like you want to store the spreadsheet itself as a binary large object in oracle. I think it can be done as well, but not using proc import.