Again on "ERROR: Unable to get SAS code. Unable to open input data"

[lc_isp]

    Hi _ALL_,

Premise: I already read both this (2014) and this (msg 39388 with fix since SAS 9.2), I'm using SAS v9.4 with EG v7.15 (old, I know, but ok).

My project is a little complex (around 220 tasks, spread into 9 steps, with around 160 tables, considering temp ones in WORK lib) so I know it can be heavy to carry out by SAS engine/server. That project needs to run everyday, and the data needs to be ready to my customers right before our office time starts: thus we're using EG-Scheduler (running server-side) to make EG work at 01:00 AM.

The very same project, up to a month ago, was running smoothly, no problems at all, producing the required Excel files in 30-40 minutes.

We made no modifications but, since a month, out customer is asking us to "rerun the project manually, please, 'cause expected data have not beed delivered". That's not happening "from time to time" (which couldn't give us that much problems) but nearly everyday, forcing us to work manually after EG-scheduler did its work (and reported NO problems, to Windows Task Scheduler, so we've no basis to re-schedule the project, too), also creating delay problems to our customer.

Asking around to my colleagues who uses SAS EG too, even if mostly manually, they also reported that EG was answering very slowly, since some time, also ending with errors but, as they're there, they can "just rerun the project", feeling not that big problem on that (needless to say, we can't ask EG-Scheduler to do the same).

What I would like to know, here, is the (possible) exact meaning of the error: SAS EG reports "ERROR: Unable to get SAS code. Unable to open input data" and, in the linked pages I read, the previous answers was about the "Unable to open input data" part, mostly. But, if I try to open a querybuilder's code which got the error, I see no code at all, like it was the code, the real problem, not the input data (as a matter of fact, the error tells "ERROR: Unable to get SAS code. Unable to open input data").

Such detail brought me to think the problem is not that my project is "too heavy about the data", but "the SAS engine is too encumbered (by other heavy jobs running at the same time) to read EG code to execute", which brings to very different solving strategies, if true... is it possible?

[ballardw]

You may have to look for an environment change, not just a SAS code or SAS setting change. In the world of clients and servers anything with an impact on the server can slow other jobs. Such as adding another program not related to this process at all that uses either the server or network bandwidth or storage space.

I would ask the IT management if any programs were added or modified that use the SAS server or what ever systems generate the files you need to read daily.

My complete guess could be that there is something delaying the creation of the files you use but the ones from the previous cycle are available. So the SAS end starts normally, using that previous data, and does what it is told.

You didn't actually describe what was received in stead of "cause expected data have not been delivered". Has your customer mentioned, or been asked, if the data delivered was identical to the previous day's data? If so something in your system is introducing either a delay on generating the "new" files in time.

FWIW, I remember some comments about companies when the transitioned to a Windows based environment had needed to replace some single servers with nearly one per program because the traffic/workload just ran poorly. This was not SAS specific. Phone systems, data bases, payroll/accounting across the board. The servers just couldn't handle the load. Perhaps an "upgrade" to operating system that was supposed to be transparent could be the cause.

You may want to contact SAS tech support to see if they can help you generate better diagnostics to see exactly what is happening in your system to isolate relevant issues.

[lc_isp]

    Hi @ballardw and TYVM for the quick reply,

You may have to look for an environment change, not just a SAS code or SAS setting change. In the world of clients and servers anything with an impact on the server can slow other jobs. Such as adding another program not related to this process at all that uses either the server or network bandwidth or storage space.

I would ask the IT management if any programs were added or modified that use the SAS server or what ever systems generate the files you need to read daily.

 We know for sure our IT is "working more" on the traditional-Host-to-SAS side, since some time (a month or two): our company data stay into their usual Host/DBMS (DB2, Oracle, etc.), to get worked on by IMS and CICS accounting transactions but, since some time, they're also copying previous-day "final output" data into SAS data-lake (so Teradata, Hadoop), so that we can access them via EG projects: such workload (which I've no idea at which times it's happening) sure encumbers SAS "servers".

 I call them "servers" but they can be whatever: from a cluster of usual linux servers, to a cloud, up to host-grade ones, still running Linux. I'm very far from the IT architecture, so I've no idea about which machines are they using, to run SAS engine(s). For sure they're using Linux as it's reported by EG, while our EG-Scheduler doesn't use classic linux cron but relies on Windows Task Scheduler (so a vbs program launching EG and passing the path/projectname to execute, along with user/password to open an EG session for the project).

Even for sure, the IT dept. working on SAS is not small, and they're using many machines, just I've no idea which grade they are but I doubt they're classic "servers".

 So "to ask the IT management" it's totally not easy at all: I opened a ticket to our HelpDesk, which escalated to some "SAS IT" but the most they answered was:
1. try scheduling in different times (giving us no clue about which hour was to better use)

2. try segmenting the Big Project into some smaller chunks, which sounds like passing the buck back to our hands without giving any objective hint on the problem (and which could take some months, also considering there is no way to "arbitrate" the various chunks, via WTS, which "just executes" but don't "manages")

My complete guess could be that there is something delaying the creation of the files you use but the ones from the previous cycle are available. So the SAS end starts normally, using that previous data, and does what it is told.

I've the same feelings: the error seems generated by some "timeout" which could happen between SAS engine and SAS-EG, 'cause the engine is too much delayed by other heavy jobs, but that's my feeling only: I've no evidences, can't have them directly, and our HelpDesk didn't give any back.

You didn't actually describe what was received in stead of "cause expected data have not been delivered". Has your customer mentioned, or been asked, if the data delivered was identical to the previous day's data? If so something in your system is introducing either a delay on generating the "new" files in time.

So, in brief: nearly everyday (on need, but often) our customer fills in an Excel file we left into his "input folder" with some contract-codes they wants to be "deep-searched" (24 months from today()) into our transactions (we're anti money landering dept.), the extracted transactions are given, raw, splitted into previous/current years (2 files), then we also group them in 4 different ways, as asked by the customer: such process outputs, starting from the single account-code, 7 different files. Contract codes are usually from 3-4 up to 15-20, so we're having 20-30 up to 140 output files per day, depending to the input.

All, up to when there was no errors, was done in 30-40 minutes so, around 2am, all files was ready: our customers (colleagues) start working ar 8am so it was all good, to them: they moved the output files into their own folders and the process was going smooth everyday (there are some "exceptions" which we're taking care, producing 2-3 output "exceptions-lists" but that's a side thing, having no real impact on the process). Files are named after their contract-codes, so no problem about overwriting them too.

Nowadays, with the error plaguing the scheduled project (but very rarely impairing us, when we execute it by hand, during the day!), in the morning our customer see the output folder empty ,if errors happened in the core-step, where data preparation is made for the following grouping ones, or they can see some files only, if the errors, which happens totally random, even when we schedule the process 2 times (1am and 3am), e.g. all codes generated 1st, 2nd, and 4th group-by data only, but all lacks 3rd grouping, etc.

For example, this morning 1am run on 6 codes produced the 2 years raw data (2 files), got error in 1st and 2nd grouping, produced 3rd and 4th group data (6+12 files), plus the exceptions ones (total 22-23 files, exceptions included). So, when my colleague arrived at office, he have been asked to manually rerun the project: he did, and EG produced the whole 54 files (49 grouped data + 2 raw yearly + 3 exception lists).

The fact, when lanched manually, the projects works seamlessy brings me to the point that there is no error, in the "application layer": the error (delay, timeout) is underneath, in the engine or DBMS layer, more probably: where we can't put our hands at all.

You may want to contact SAS tech support to see if they can help you generate better diagnostics to see exactly what is happening in your system to isolate relevant issues.

I've no direct access to SAS tech support, AFAIK. I'm trying to ask to some IT colleagues I know but, as I'm not their "direct customer" (no contract, no budget, to them) they may give me a favor, to answer, of even won't: not their problem.

[ballardw]

If you don't have direct access to SAS tech support your SAS Admin really should.

You description involving what appears to be multiple data sources gives me headaches just imagining all the places things can go wrong.

Something you might be able to look at. When you say that running the system manually goes okay then I would investigate ALL of the SAS system options used for the job. You might find one that is more related to batch processing that terminates when minor problems are encountered that a more interactive job doesn't (your Exceptions perhaps). I don't use EG so can't point to specific ones to look for involvement.

You may also consider turning on as many of the log options, like Fullstimer and connection diagnostics to see if the job SAS logs would point to bottlenecks or suspect areas.

I can certainly understand the frustration. I am "the client" for a process where a data provider has to write one file to our secure FTP server. We didn't get any files for 5 months. It took that long for my ID side of things to realize the reason the provider couldn't log into the server because their system passed through and international server and our SFTP was configured to block all such. 5 months because our IT was that slow on "tracing".

[lc_isp]

@ballardw 

If you don't have direct access to SAS tech support your SAS Admin really should.

Correct.

You description involving what appears to be multiple data sources gives me headaches just imagining all the places things can go wrong.

At least, this is a batch project, so user-input is really to the minimum.

Something you might be able to look at. When you say that running the system manually goes okay then I would investigate ALL of the SAS system options used for the job. You might find one that is more related to batch processing that terminates when minor problems are encountered that a more interactive job doesn't (your Exceptions perhaps). I don't use EG so can't point to specific ones to look for involvement.

The "exceptions" are not system-side: they're recognized data which we lack the rules to manage: we choosen to export such data into separate files, so that our customer have a chance to tell us what to do with them (or even to insert such rules by his own: we left config files available to them for this purpose).

You may also consider turning on as many of the log options, like Fullstimer and connection diagnostics to see if the job SAS logs would point to bottlenecks or suspect areas.

I'm using "OPTIONS mprint mprintnest mlogic mlogicnest symbolgen syntaxcheck autocorrect fullstimer source stimer threads" in production environment (batch scheduling), is there anything more I should enable?

I can certainly understand the frustration. I am "the client" for a process where a data provider has to write one file to our secure FTP server. We didn't get any files for 5 months. It took that long for my ID side of things to realize the reason the provider couldn't log into the server because their system passed through and international server and our SFTP was configured to block all such. 5 months because our IT was that slow on "tracing".

Our main problem is our IT Support is tracing nothing at all: they're also not answering in a viable way (AKA giving solutions) to direct inquiry (tickets): we're probably go escalate the solutions search to higher structure levels, as the fix seems far outside our action's perimeter.

@SASKiwi Thanks for your words, we're going in that direction too (hopefully), but it's not just a technical choice: we had to set a budget for that "improved support" level, by one of our departments (as our group have an IT dept. which is a company by its own, like it happens frequently, with large companies/groups). AFAIK such IT dept. uses SAS Management Console and related programs/scheduler, which should be an higher level than our current.

[EDIT]

I forgot to add, in my reply: I (re)opened this thread mostly to understand if the EG error was only about "data it can't read" or involves the "code to be executed (by the engine)": the error also states "Unable to get SAS code". I would like to have a deeper insight on it, if possible (even reference links, which I still have not found).

[ballardw]

If you aren't still debugging macros I might turn off the macro options. Doubt if they add much to processing problems but can create a very full log that might be an issue.

I'm using "OPTIONS mprint mprintnest mlogic mlogicnest symbolgen syntaxcheck autocorrect fullstimer source stimer threads" in production environment (batch scheduling), is there anything more I should enable?

You may want to include a call to Proc Options to get a list of all of the settings for all options in that batch job and your interactive session and go over the two results line by line. If there are system options impacting your problem I would suspect something related to connections or networking but there are enough of those that I'm not sure where to start. The differences may give you and your staff a few clues.

[SASKiwi]

IMHO you should consider migrating your SAS EG projects to using server-based batch scheduling when productionising them. This will provide a more robust, reliable, industrial-strength environment rather than relying on PC-based tools where so much more can go wrong.

Where I work, EG is purely a development tool. To productionise and run SAS applications regularly, we deploy the programs to run as batch jobs with server-based schedulers using SAS Management Console. That way, when things go wrong the evidence is in the batch logs and is typically easy to diagnose and fix.