can library name be parameterized in sas datasets?

[Sandhyaa]

We have a requirment to use same jobs for same type of tables from two different database schemas of SQL Server. So can we use the same jobs by parameterizing the library reference of the tables in SAS DI? Is this possible?

[LinusH]

Not directly in DIS. Table names is possible though.

Depending on how you are setting the parameter, you could have the same libname defined with schema as a macro/environtment variable. With this scenario, you probably need to mark this libname as pre-assigned, and handle the logic in appserer:

[Patrick]

Same basic idea like Linus:

Define a library where you use a macro variable name as schema name. Then define a default value for this macro in the autoexec (so that you can register tables).

Do not pre-assign the library as this will allow you to set the value in the schemaname macro variable during run time at the beginning of your job (eg. as parameter of the DIS job and then you have a outer loop job calling this job passing the schema name you need).

[Sandhyaa]

Patrick,

We are creating a library for SQL server database using ODBC Engine. Where to define the macro variable for the schema while creating the Library?

[LinusH]

Sadly, there is no silver bullet.

One thing to consider is how do want/need to access the data when loaded? What kind of clients/jobs do you have?

What is the difference between those schemas, which to use when, and why?

If you have parameters in the jobs, only those jobs will be able to access the data.

Perhaps do you need some default behavior, which could be set by some logic in the appserver_autoexec.

[Patrick]

@Sandhyaa

In the library definition under Data Server/Database Schema use instead of the schema name something like &db_schema_1. Then in the autoexec define and populate this macro variable with the schema name you want to use for registering the tables.

I would use an autoexec which gets processed by all servers (at least the batch, stored process and workspace server should get it) so I would use ..\Config\Lev<n>\<server, eg. SASApp>\appserver_autoexec_usermods.sas and then

%let db_schema_1=<schema name>;