BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nigel_Pain
Lapis Lazuli | Level 10

SAS 9.4M5 on Windows 2012R2

We created a library using ODBC to link to a SQL Server database, and registered table metadata using SASMC. Subsequently one of the users updated the metadata using PROC METALIB code in Enterprise Guide. This caused the name of one of the variables to change. Its database name has an embedded space and SASMC replaced that with an underscore. But the code in EG replaced the space. I'm guessing this is because of the VALIDVARNAME option, which in our workspace servers is set to ANY. I'm guessing that when SASMC runs the code it's set to V7. But I don't know where this option is set for SASMC. Beyond changing the option setting for workspace servers, is there anything we can do? I've told the user in question to set VALIDVARNAME=V7 in code before updating metadata (and set back to ANY afterwards), as an interim solution, but I don't know if there's anything better that can be done.

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

@Nigel_Pain  - I've always avoided registering database tables in SMC for the very reasons you are having. If you do that, then the variables names will vary based on what the user's SAS session's VALIDVARNAME setting is which seems fine to me.

View solution in original post

6 REPLIES 6
AnandVyas
Ammonite | Level 13

Hi @Nigel_Pain 

 

SMC uses the workspace server application context tagged to the library. You can view the log from the SMC wizard after running the update table metadata step. IMO don't think there is any explicit option added for SMC.

Nigel_Pain
Lapis Lazuli | Level 10

Hi @AnandVyas 

That's very odd then, because it should be the same as EG. Yet, when table metadata is updated in SASMC it's replacing spaces with underscores, which I would expect was because VALIDVARNAME was set to V7. When it's done in EG the spaces are retained, because VALIDVARNAME is set to ANY.

SASKiwi
PROC Star

@Nigel_Pain  - I've always avoided registering database tables in SMC for the very reasons you are having. If you do that, then the variables names will vary based on what the user's SAS session's VALIDVARNAME setting is which seems fine to me.

Nigel_Pain
Lapis Lazuli | Level 10

Hi @SASKiwi , you are probably right, but the issue had never occurred to me before. We have quite a large (and increasing!) number of ODBC libraries, because there are a couple of databases which use different schemas to control access to different sets of data. The standard instructions for creating a new one (penned by myself, I have to admit) is to finish off by registering the tables, using SASMC because that's where everything else has been done. But in retrospect it's probably better to do that in EG or Studio. However, I think I'll advise that people set VALIDVARNAME=V7 when registering or updating table metadata. Makes it easier for people writing code when they maybe aren't aware of name literals.

Thanks for your advice.

SASKiwi
PROC Star

@Nigel_Pain  - Not sure if you know this trick, but if you are assigning database LIBNAMEs in metadata then adding the extra attribute AssignMode = 2 in the metadata definition will ensure tables show in the EG server list without having to register them.

Nigel_Pain
Lapis Lazuli | Level 10

That's worth a look, @SASKiwi. I'll have a play when I have a moment (just preparing for a UIP to M7 this weekend)

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 966 views
  • 0 likes
  • 3 in conversation