Table metadata changing

[Nigel_Pain]

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.

[AnandVyas]

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]

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 · Accepted solution]

@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]

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]

@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]

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)