topic Maximum number of lines in a Stored Process in Developers

Maximum number of lines in a Stored Process

mastropi — Tue, 09 Aug 2016 15:12:39 GMT

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?

Thanks

Daniel

System:

Windows 7 (64-bit)

SAS 9.4 (64-bit)

12 GB RAM

Re: Maximum number of lines in a Stored Process

Quentin — Tue, 09 Aug 2016 15:17:56 GMT

When you run the full code and get the "no results" error, do you see any error messages in the SAS log? At what point does it stop?

Re: Maximum number of lines in a Stored Process

mastropi — Tue, 09 Aug 2016 15:22:38 GMT

Good question. There are no errors in the truncated log file. Following are the last few lines of the log file I generate with PROC PRINTTO:
(note that the log is truncated in the middle of the FORMAT procedure, furthermore, in the middle of a format definition)

1049 VALUE F61_FOR
1050
1051 /*LOW--1 = '001- hasta -1'
1052 -1<-0 = '002- hasta 0'
1053 0<-16 = '003- hasta 16'
1054 16<-29 = '004- hasta 29'
1055 29<-30 = '005- hasta 30'
1056 30<-31 = '006- hasta 31'
1057 31<-HIGH = '007- hasta HIGH';*/
1058
1059 LOW-0 = '001- hasta 0'
1060 0<-15 = '002- hasta 15'
1061 15<-30 = '003- hasta 30'
1062 30<-HIGH = '004- hasta HIGH';
NOTE: Format F61_FOR has been output.
1063
Sistema SAS

1064
1065 VALUE F62_FOR
1066
1067 /*LOW--11 = '001- hasta -11'
1068 -11<--1 = '002- hasta -1'
1069 -1<-0 = '003- hasta 0'
1070 0<-11 = '004- hasta 11'
1071 11<-18 = '005- hasta 18'
1072 18<-47 = '006- hasta 47'
1073 47<-104 = '007- hasta 104'
1074 104<-194 = '008- hasta 194'
1075 194<-319 = '009- hasta 319'
1076 319<-494 = '010- hasta 494'
1077 494<-HIGH = '011- hasta HIGH';*/
1078 LOW-0 = '000- hasta 0'
1079 0<-15 = '001- hasta 15'
1080 15<-31 = '002- hasta 31'
1081 31<-46 = '003-
NOTE: Format F62_FOR has been output.
NOTE: FORMAT PROCEDURE used (Total process time):
real time 0.07 seconds
cpu time 0.01 seconds

Re: Maximum number of lines in a Stored Process

Quentin — Tue, 09 Aug 2016 18:33:15 GMT

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.

Re: Maximum number of lines in a Stored Process

mastropi — Thu, 11 Aug 2016 19:39:22 GMT

Hello Quentin,

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.

Daniel

Re: Maximum number of lines in a Stored Process

LinusH — Mon, 15 Aug 2016 04:21:08 GMT

Having 3000 lines SAS programs sounds like maintenance nightmare. My first action would bd trying to shorten it radically.
An obvious one would be to store your formats in a permanent location, or athe least use CNTLIN to make it data driven.

Re: Maximum number of lines in a Stored Process

mastropi — Mon, 15 Aug 2016 10:23:39 GMT

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.

Thank you

Daniel

Re: Maximum number of lines in a Stored Process

Quentin — Mon, 15 Aug 2016 13:46:01 GMT

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#n0tzurc8qfze0vn13z4n6j6mzz14.htm). 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.

Re: Maximum number of lines in a Stored Process

LinusH — Tue, 16 Aug 2016 06:13:55 GMT

As @Quentin points out, meta bound libraries are an option. That is the top notch security you can get in Base SAS data sets.
If that's seem to complicated you can always secure the data set files on the OS level. Then you can also secure a permanent format catalog.