I have a script that runs a SAS passhtrough query that connects to an Oracle database. This was part of a cronjob that runs on a Unix server, and has had no issues for years. In the past few weeks however, the job has started hanging up on this one particular step - according to logs it used to take about 15 seconds to run but now just will run indefinitely before we have to kill the job. There are no associated errors or warnings in the log - the job will create a lockfile and just run indefinitely until we have to kill it.
The step where the job hangs up is pasted in below. There are two macro variables &start_dt and &end_dt, which represent the date range the job is pulling sales data for.
While investigating, we tried a few different approaches, and were able to get this step to run successfully and in its usual time by changing three things:
running the script through an Enterprise Guide client which connects to the same server as opposed to running the script via CLI / shell script
Changing the library the step writes to to work instead of writing
the dataset to salesdata library (as seen in code below)Changing the dates to hardcoded values instead of macro variables.
As for the date variables themselves, they are strings in date9 format, e.g &start_dt = '08-May-22', &end_dt = '14-May-22'. Initially I suspected the issue was related to the way the dates are structured since this is an older project I have inherited, but am confused to why the job ran without issue for so long up until a few weeks ago, even with these oddly formatted date macro vars.
The other possibility I considered was that some sort of resource on the unix server was getting locked up when it got to this step, potentially from some sort of hanging job or some other conflict with an older file such as a log or a previous sas dataset.
Problematic version of the step in the script pasted below:
PROC SQL;
connect to oracle(user=&uid pass=&pwd path='@dw');
create table salesdata.shipped as
Select
SKN_NBR,
COLOR_NBR,
SIZE_NBR,
SALESDIV_KEY,
ORDER_LINE_QTY as QUANTITY label="SUM(ORDER_LINE_QTY)",
EX1 as DOLLARS label="SUM(EX1)" from connection to oracle(
select
A1."SKN_NBR",
A1."COLOR_NBR",
A1."SIZE_NBR",
decode(A1."SALESDIV_KEY", 'ILB', 'IQ',
'IQ ', 'IQ',
'IQC', 'IQ',
'ISQ', 'IQ',
'IWC', 'IQ',
'QVC'),
SUM(A1."ORDER_LINE_QTY"),
SUM(A1."ORDER_LINE_QTY" * A1."ORDER_LINE_PRICE_AMT")
from DW.ORDERLINE A1, DISTINCT_SKN A2, DW.ORDERSTATUSTYPE A3
where
A2."SKN_NBR" = A1."SKN_NBR" AND
A1."CURRENT_STATUS_DATE" Between &start_dt and &end_dt AND
A1."ORDERLINESTATUS_KEY" = A3."ORDERLINESTATUS_KEY" AND
A3."ORDERSTATUS_SHIPPED" = 'Y' AND
A1."ORDER_LINE_PRICE_AMT" > 0
group by A1."SKN_NBR",
A1."COLOR_NBR",
A1."SIZE_NBR",
decode(A1."SALESDIV_KEY", 'ILB', 'IQ',
'IQ ', 'IQ',
'IQC', 'IQ',
'ISQ', 'IQ',
'IWC', 'IQ',
'QVC')
order by A1."SKN_NBR",
A1."COLOR_NBR",
A1."SIZE_NBR",
decode(A1."SALESDIV_KEY", 'ILB', 'IQ',
'IQ ', 'IQ',
'IQC', 'IQ',
'ISQ', 'IQ',
'IWC', 'IQ',
'QVC')
) as t1(SKN_NBR, COLOR_NBR, SIZE_NBR, SALESDIV_KEY, ORDER_LINE_QTY, EX1)
;
disconnect from oracle; quit;
[1]: https://i.stack.imgur.com/GGjin.jpg
CodePudding user response：
What style you need to use for date constants in Oracle depends on your settings in Oracle. But normally you can use expressions like one of these
date '2022-05-14'
'2022-05-14'
You seem to claim that on your system you can use values like
'14-May-22'
(how does Oracle know what century you mean by that?).
Note that in Oracle it is important to use single quotes around constants as it interprets strings in double quotes as object names.
So if you have a date value in SAS just make sure to make the macro variable value look like what Oracle wants.
For example to set ENDDT to today's date you could use:
data _null_;
call symputx('enddt',quote(put(today(),date11.),"'"));
run;
Which would the same as
%let enddt='17-MAY-2022';
CodePudding user response：
So @Tom answer was helpful - it appears that our DBAs updated some settings a few weeks back that impacted how stringent Oracle is in terms of which date formats are accepted.
For what it's worth, the date macro vars were being constructed on the fly using a clunky data step that read off of a date key dataset:
You'll notice the last piece of the date string being put together for bost variables uses year2. format, so just the last two digits of the year.
To @Tom's point, this is apparently confusing Oracle in terms of which century its in, so the job gets hung up.
data dateparm;
set salesdata.week_end_date;
start = "'" || put(day(week_end_date - 6), z2.) || '-' || put(week_end_date - 6, monname3.) || '-' ||
put(week_end_date - 6, year2.) || "'";
end = "'" || put(day(week_end_date), z2.) || '-' || put(week_end_date, monname3.) || '-' ||
put(week_end_date, year2.) || "'";
call symput('start_dt', start);
call symput('end_dt', end);
run;
Once I changed this step to use year4. format for the last piece, the job was able to run fine without incident on both unix and E guide. Example below:
data dateparm;
set npdd.week_end_date;
start = "'" || put(day(week_end_date - 6), z2.) || '-' || put(week_end_date - 6, monname3.) || '-' ||
put(week_end_date - 6, year4.) || "'";
end = "'" || put(day(week_end_date), z2.) || '-' || put(week_end_date, monname3.) || '-' ||
put(week_end_date, year4.) || "'";
call symput('start_dt', start);
call symput('end_dt', end);
run;