Capturing LibName into macro variable SAS DI Studio

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Capturing LibName into macro variable SAS DI Studio

Hello everyone,

I'm looking for a way to capture the library name into a macro variable to use within SAS DI pre/post code instead of having it hard coded.

Any ideas on the best or good approach for this?

LIBNAME libref SQLSVR  Datasrc="&datasrc"  SCHEMA=Name  USER="domain\user"  PASSWORD="" ;

Regards,

Jonathan


Accepted Solutions
Solution
‎10-07-2013 03:28 PM
Super User
Posts: 3,115

Re: Capturing LibName into macro variable SAS DI Studio

If these LIBNAMES are common to a lot of jobs, and are perhaps useful for other SAS users it would probably make more sense to define them in SAS metadata via SAS Management Console as pre-assigned libraries. You can use the DEFER = YES libname option to ensure that they are only actioned when the LIBREFs are referenced to avoid unnecessary overhead. Then you avoid defining them in DI altogether.

If you think this is a good approach for you I would also suggest avoiding the DATASRC = option as under Windows these data sources need to be maintained in the ODBC administration tool, another point of maintenance (Warning: if working on Unix ignore this suggestion as you have to define data sources). Instead you can refer to the SQL Server directly. The following example also assumes that you are using Windows Authentification:

Libname SQLSERVR (noprompt = "server=MySQLServer;DRIVER=SQL Server;Trusted Connection=yes;") database = "MyDatabase" schema = "MySchema";

View solution in original post


All Replies
Solution
‎10-07-2013 03:28 PM
Super User
Posts: 3,115

Re: Capturing LibName into macro variable SAS DI Studio

If these LIBNAMES are common to a lot of jobs, and are perhaps useful for other SAS users it would probably make more sense to define them in SAS metadata via SAS Management Console as pre-assigned libraries. You can use the DEFER = YES libname option to ensure that they are only actioned when the LIBREFs are referenced to avoid unnecessary overhead. Then you avoid defining them in DI altogether.

If you think this is a good approach for you I would also suggest avoiding the DATASRC = option as under Windows these data sources need to be maintained in the ODBC administration tool, another point of maintenance (Warning: if working on Unix ignore this suggestion as you have to define data sources). Instead you can refer to the SQL Server directly. The following example also assumes that you are using Windows Authentification:

Libname SQLSERVR (noprompt = "server=MySQLServer;DRIVER=SQL Server;Trusted Connection=yes;") database = "MyDatabase" schema = "MySchema";

Super User
Posts: 5,260

Re: Capturing LibName into macro variable SAS DI Studio

I'm not sure if I totally agree that you libname s are hard-coded in DI jobs. They certainly look that way in the deployed code, but the deployment looks into the metadata to construct it.

But well, if you chose to change a library definition, you need to re-deploy all jobs that has any tables refering to it. The Analyze tool can help you on that.

As suggests, you can avoid this extra management by having pre-assigned libraries. If you are on 9.3 or later, there is an options that allows you to specify libraries pre-assigned using metaautoresources - this will let you maintain library definitions in one place - opposed to having the defines as "hard coded" in an autoexec, which was quite common in 9.1 and 9.2 environments.

Data never sleeps
Respected Advisor
Posts: 3,902

Re: Capturing LibName into macro variable SAS DI Studio

As SASKiwi and Linus already suggested:

Define a pre-assigned library in Metadata and then use the libref in your code. A libref should not change so no need to pack this one into a macro variable.

Actually: Pre- and post-code gets generated after the definition of the &input and &output variables. So if you're using such code in a node against which you've also registered table metadata then you've got already all information available at the point where your pre-code gets inserted. And from a DI perspective: You shouldn't write pre- or post-code which manipulates a table which is not registered against the node where you manipulate it (eg. because else impact analysis won't work).

Contributor
Posts: 70

Re: Capturing LibName into macro variable SAS DI Studio

SASKiwi,

Our environment at this point is a mixture of Linux and windows.

SAS is installed on Linux and the files we load data from are sitting on Windows.

I brought up the idea suggested to use pre-assigned libraries and I'm awaiting feedback.

LinusH,

I completely agree and thus we are looking to move away from having it hardcoded within the DI jobs.

Thanks guys I will update with progress and or issues.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 676 views
  • 6 likes
  • 4 in conversation