topic Re: Stored Procedure Error because of Blank in Info Map Name in SAS Enterprise Guide

Stored Procedure Error because of Blank in Info Map Name

JSJ — Tue, 06 Sep 2016 18:24:48 GMT

Hi,

We have been creating stored procedures for a long time, but recently we are getting an error. It appears that if our info map has a space in the name, the stored procedure errors.

My colleague created a SAS EG project that completes successfully. Next, she tried to create a stored procedures in order to load the report to our SAS Portal.

When she tried to create the stored procedure, she got an error (see attached)

An error occured executing the stored process job " Alumni Career Services Report ". The stored process library failed to achieve the results...... etc.

The log shows an error (See attached)

The value 'Annual Giving' is not a valid SAS name.

Our IT folks say to work around this by renaming the output from the import of the info map to have an underscore instead of a blank. Then it in turn will create a workfile with an underscore instead of a blank. Unfortunately, this may mean that we have to link multiple files again to get this working (in some cases, recreate the entire report).

Has anyone had this problem?

Thanks,

Re: Stored Procedure Error because of Blank in Info Map Name

ChrisHemedinger — Tue, 06 Sep 2016 18:29:08 GMT

Try adding:

options validvarname=ANY;

To the start of your stored process code.

Re: Stored Procedure Error because of Blank in Info Map Name

JSJ — Tue, 06 Sep 2016 18:45:44 GMT

We will try it out. Thanks for your quick response!

Re: Stored Procedure Error because of Blank in Info Map Name

JSJ — Mon, 12 Sep 2016 19:28:12 GMT

No success yet!

Now when we do a listdata in SAS EG we are getting an error on the infomap name with a space in it.

We tried putting the code in the listdata code, but it doesn't change anything.

Could it be because it already has an options statement running in the background that we cannot update?

Thanks!

Re: Stored Procedure Error because of Blank in Info Map Name

ChrisHemedinger — Mon, 12 Sep 2016 19:42:22 GMT

So this is running in EG, not in a Stored Process? Any chance you can post the log?

Re: Stored Procedure Error because of Blank in Info Map Name

JSJ — Mon, 12 Sep 2016 19:51:50 GMT

Hi Chris-

Attached is the copy of the log with the error.

We write SAS EG Projects and then produce a listdata. Then we create a stored procedure in that same EG Project.

I did find a support document that states that the option statement is the default for SAS EG, but not for stored procedures.

http://support.sas.com/kb/32/613.html

It looks like to me that our IT folks will have to put in an over ride on this.

Thanks in advance for your help!

Re: Stored Procedure Error because of Blank in Info Map Name

ChrisHemedinger — Mon, 12 Sep 2016 20:03:08 GMT

You can specify the OPTIONS VALIDVARNAME option in code any time. So you can set it to V7 when accessing the Information Map initially (which should generate names that comply with the traditional SAS naming conventions), then set it back to ANY when you're all done with that.

Try running the report example I shared here, Preface the code with OPTIONS VALIDVARNAME=V7; and see what happens...compare the output to the same when you run it with OPTIONS VALIDVARNAME=ANY.

Re: Stored Procedure Error because of Blank in Info Map Name

JSJ — Tue, 13 Sep 2016 18:45:01 GMT

Gosh, Chris, I don't see a report example. Can you tell me where to find it?

I did find a SAS support doc that refers to having IT make an environmental change so that we (the report writers) don't need to update and rewrite our projects!

http://support.sas.com/kb/32/613.html

Here is what it says:

By default, SAS Enterprise Guide executes the following statement:

OPTIONS VALIDVARNAME=ANY;

This setting of the VALIDVARNAME system option allows the use of column names that contain embedded spaces and special characters. However, when executing a stored process that was created and registered in SAS Enterprise Guide from another BI client application, code that worked from SAS Enterprise Guide might fail with the following error:

ERROR: The value <variable name with space or special character> is not a valid SAS name.

This error occurs because the workspace and stored process servers do not automatically set OPTIONS VALIDVARNAME=ANY; prior to executing stored process code.

To avoid problems with stored processes that require the VALIDVARNAME=ANY system option setting, you can apply one of the following approaches:

Specify OPTIONS VALIDVARNAME=ANY; at the top of the stored processes that use special variable names.
Specify OPTIONS VALIDVARNAME=ANY; in an autoexec file that is used for your workspace server and stored process server.
Specify –VALIDVARNAME ANY as an invocation option on the command line that invokes your workspace and stored process server.
Specify –VALIDVARNAME ANY in a sasv9.cfg file that is used in your BI environment.

Re: Stored Procedure Error because of Blank in Info Map Name

JSJ — Tue, 13 Sep 2016 18:46:26 GMT

Gosh, Chris, I don't see a report example here http://blogs.sas.com/content/sasdummy/2010/05/25/reporting-on-sas-information-maps/. Can you tell me where to find it?

I did find a SAS support doc that refers to having IT make an environmental change so that we (the report writers) don't need to update and rewrite our projects!

http://support.sas.com/kb/32/613.html

Here is what it says:

By default, SAS Enterprise Guide executes the following statement:

OPTIONS VALIDVARNAME=ANY;

ERROR: The value <variable name with space or special character> is not a valid SAS name.

This error occurs because the workspace and stored process servers do not automatically set OPTIONS VALIDVARNAME=ANY; prior to executing stored process code.

To avoid problems with stored processes that require the VALIDVARNAME=ANY system option setting, you can apply one of the following approaches:

Specify OPTIONS VALIDVARNAME=ANY; at the top of the stored processes that use special variable names.
Specify OPTIONS VALIDVARNAME=ANY; in an autoexec file that is used for your workspace server and stored process server.
Specify –VALIDVARNAME ANY as an invocation option on the command line that invokes your workspace and stored process server.
Specify –VALIDVARNAME ANY in a sasv9.cfg file that is used in your BI environment.

Re: Stored Procedure Error because of Blank in Info Map Name

ChrisHemedinger — Tue, 13 Sep 2016 19:01:39 GMT

Direct link to the program:

http://support.sas.com/documentation/onlinedoc/guide/examples/infomapReportProgram.htm

And an example of output:

http://support.sas.com/documentation/onlinedoc/guide/examples/infomapReport.htm

Re: Stored Procedure Error because of Blank in Info Map Name

JSJ — Tue, 13 Sep 2016 19:11:17 GMT

Thanks!

We only produce reports in SAS EG in my area. We don't create reports with base SAS.

Do you think we can still use the Options statement in SAS EG?

My hope is our IT Department can make it the default in our environment.

What is interesting is this has not been a problem until a few weeks ago. We have had the infomaps with the space in the name for many years.

Thanks!

Re: Stored Procedure Error because of Blank in Info Map Name

ChrisHemedinger — Tue, 13 Sep 2016 19:14:38 GMT

Yes! Paste code I provided into a new Program window and see how it does when you run it. You can run any code in SAS EG -- doesn't have to be just in Base SAS. And you can use the OPTIONS statement in EG -- in code.

Re: Stored Procedure Error because of Blank in Info Map Name

JSJ — Tue, 13 Sep 2016 21:33:43 GMT

We have possibly found the problem. Some of us were

Choosing compatible on the TOOLS>OPTIONS>DATA GENERAL >Valid Member Names.

This doesn't allow blanks in the names.

What I don't know is the scope of this option under tools.

It appears there are 2 environments - SAS EG and the creation of stored procedures.

Thanks so much, Chris!

Re: Stored Procedure Error because of Blank in Info Map Name

ChrisHemedinger — Tue, 13 Sep 2016 21:55:35 GMT

Glad you're making progress. That option generates OPTIONS VALIDMEMNAME= statements -- so you can add that at the front of your Stored Process code to ensure consistent results. Values are COMPATIBLE vs EXTEND.

I recommend COMPATIBLE unless you absolutely need the table names to be "friendlier" to read -- it's easier for downstream coding.

Re: Stored Procedure Error because of Blank in Info Map Name

JSJ — Tue, 13 Sep 2016 22:14:04 GMT

We have an infomap that is named with a blank in it that we use in almost all our reports!

What is the downside of using extended vs. compatible? We rarely type a name of a file in EG so I don't know how this would

hurt us downstream!

Thanks!