Desktop productivity for business analysts and programmers

Stored Procedure Error because of Blank in Info Map Name

Reply
Contributor JSJ
Contributor
Posts: 43

Stored Procedure Error because of Blank in Info Map Name

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,

Jo

 

 

Community Manager
Posts: 2,693

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

[ Edited ]

Try adding:

 

options validvarname=ANY;

To the start of your stored process code.

Contributor JSJ
Contributor
Posts: 43

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

We will try it out. Thanks for your quick response!
Contributor JSJ
Contributor
Posts: 43

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

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!

Jo

Community Manager
Posts: 2,693

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

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

Contributor JSJ
Contributor
Posts: 43

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

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!

Jo

Community Manager
Posts: 2,693

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

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.

Contributor JSJ
Contributor
Posts: 43

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

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!

 

 
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:

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

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

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!

 

 
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:

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

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

Contributor JSJ
Contributor
Posts: 43

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

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!

Jo

Community Manager
Posts: 2,693

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

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. 

Contributor JSJ
Contributor
Posts: 43

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

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!

Jo

Community Manager
Posts: 2,693

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

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.

Contributor JSJ
Contributor
Posts: 43

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

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!

Jo

Ask a Question
Discussion stats
  • 14 replies
  • 1100 views
  • 2 likes
  • 2 in conversation