08-09-2016 11:12 AM
Hello, is there a maximum number of lines that can be submitted as part of a SAS stored process (SP) in SAS Enterprise Guide?
I am asking because I run a SAS code containing ~ 3,000 lines with no errors under normal SAS code submission but when I run it as an SP, the SP stops with an error pop-up window stating that no results were generated. In addition, the generated log is truncated to a file whose size is just 50 kb (compared to 300 kb under the aforementioned regular sas submission).
If I remove from the SP code ~ 2000 lines from the middle of the code that correspond to one single PROC FORMAT statement and replace the lines with an %INCLUDE of a separate code that defines the formats, the SP runs without any errors (now it has ~ 1,000 lines).
If I run an SP that contains just the PROC FORMAT spanning 2,000 lines, the SP completes successfully.
So, is there a limit of the number of lines that an SP can contain that lies somewhere between 2,000 and 3,000 lines?
Windows 7 (64-bit)
SAS 9.4 (64-bit)
12 GB RAM
08-09-2016 11:22 AM
08-09-2016 02:33 PM
That's truly bizarre. If you don't get answers here, I would try posting over in the Enterprise Guide forum.
Just for curiousity/sake of testing, you might try submitting the same code in EG just as code submission not running as a stored process. That could help separate whether it is an EG stored process issue or an EG code submission issue.
Also, if you have some other way to run the stored process (e.g. via stored process web app), you might try running it that way, to see if it happens only when a stored process is submitted via EG.
If I had to guess, I would guess the problem only happens when the stored process is submitted via EG. At least that is what I would hope.
Another thought would be to delete four rows from the format, and see if it always stops on the same statement, or if it is stopping at a certain row number of the code. I suppose it's possible that there is some unseen control character which is causing SAS to think the code submission is over. So you might try deleting just the last line, 1081 31<-46 = '003-, and maybe one or two lines on either side, and see if that magically makes the whole program work.
08-11-2016 03:39 PM
Thank you for your thoughts.
Your first suggestion is exactly what I had tried: running the process through normal code submission in SAS EG works perfectly fine, so it's definitely a Stored Process issue.
As per your second suggestion, unfortunately I don't think I have other ways of running a stored process other than from SAS EG.
Regarding your third suggestion, if the unseen control character were the cause of the problem, the PROC FORMAT statement (which contains 2,000 lines) would also give an error or stop when run as a Stored Process on its own... but it actually runs successfully.
08-15-2016 12:21 AM
08-15-2016 06:23 AM
Sure, those are valid options as well. Thanks for pointing them out.
Our approach (as a team inside a large organization) to put as much code as possible inside the Stored Process is mostly to handle security in one place, i.e. who is allowed to change the stored process and all related input data --such as the formats-- thus decreasing the probability of accidental modifications.
Now I see that we can define read-only datasets (to be used e.g. as input to PROC FORMAT in the CNTLIN option) with the ALTER= option of the created dataset in a data step. But this requires a password that we need to remember or store somewhere (as opposed to handle security requirements solely through SAS Management Console).
Another option would be to store the formats in a read-only 'formats' catalog. Since I haven't found a way of setting read-only status to a catalog, at least it can be simulated using SAS Managemenet Console to map a dedicated library name to the location of the 'formats' catalog with option access=readonly. But still I user could separately map that location with read/write access and change the formats... unless there is a way in SAS Management Console of defining read/write access rules to a directory sitting in the SASApp server where the application is run?
If you have any other suggestions about security handling, I would be keen to hear.
08-15-2016 09:46 AM
If you go the PROC FORMAT CNTLIN approach you can apply metadata permissions to the dataset (assuming you register the data in the metadata), the same way as you can apply metadata permissions to a stored process.
If you're on 9.4 you could look into metadata-bound libraries (http://support.sas.com/documentation/cdl/en/seclibag/66930/HTML/default/viewer.htm#n0tzurc8qfze0vn13...). Of course you would want to control access to the data at the OS-level as well.
I would agree that maintenance / testing / documentin would be easier if you store the format information in datasets. That said, I would suggest asking this question over in the EG forum, or contacting SAS support. If EG is choking on a long format, it would be good for them to know.
08-16-2016 02:13 AM