Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
mm_assigndirectlib.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Assigns library directly using details from metadata
4 @details Queries metadata to get the libname definition then allocates the
5 library directly (ie, not using the META engine).
6 usage:
7
8 %mm_assignDirectLib(MyLib);
9 data x; set mylib.sometable; run;
10
11 %mm_assignDirectLib(MyDB,open_passthrough=MyAlias);
12 create table MyTable as
13 select * from connection to MyAlias( select * from DBTable);
14 disconnect from MyAlias;
15 quit;
16
17 <h4> SAS Macros </h4>
18 @li mf_getengine.sas
19 @li mp_abort.sas
20
21 @param [in] libref the libref (not name) of the metadata library
22 @param [in] open_passthrough= () Provide an alias to produce the CONNECT TO
23 statement for the relevant external database
24 @param [in] sql_options= () Add any options to add to proc sql statement,
25 eg outobs= (only valid for pass through)
26 @param [in] mDebug= (0) set to 1 to show debug messages in the log
27 @param [in] mAbort= (0) set to 1 to call %mp_abort().
28
29 @returns libname statement
30
31 @version 9.2
32 @author Allan Bowe
33
34**/
35
36%macro mm_assigndirectlib(
37 libref
38 ,open_passthrough=
39 ,sql_options=
40 ,mDebug=0
41 ,mAbort=0
42)/*/STORE SOURCE*/;
43
44%local mD;
45%if &mDebug=1 %then %let mD=;
46%else %let mD=%str(*);
47%&mD.put Executing mm_assigndirectlib.sas;
48%&mD.put _local_;
49
50%if &mAbort=1 %then %let mAbort=;
51%else %let mAbort=%str(*);
52
53%&mD.put NOTE: Creating direct (non META) connection to &libref library;
54
55%local cur_engine;
56%let cur_engine=%mf_getengine(&libref);
57%if &cur_engine ne META and &cur_engine ne and %length(&open_passthrough)=0
58%then %do;
59 %put NOTE: &libref already has a direct (&cur_engine) libname connection;
60 %return;
61%end;
62%else %if %upcase(&libref)=WORK %then %do;
63 %put NOTE: We already have a direct connection to WORK :-) ;
64 %return;
65%end;
66
67/* need to determine the library ENGINE first */
68%local engine;
69data _null_;
70 length lib_uri engine $256;
71 call missing (of _all_);
72 /* get URI for the particular library */
73 rc1=metadata_getnobj("omsobj:SASLibrary?@Libref ='&libref'",1,lib_uri);
74 /* get the Engine attribute of the previous object */
75 rc2=metadata_getattr(lib_uri,'Engine',engine);
76 putlog "mm_assigndirectlib for &libref:" rc1= lib_uri= rc2= engine=;
77 call symputx("liburi",lib_uri,'l');
78 call symputx("engine",engine,'l');
79run;
80
81/* now obtain engine specific connection details */
82%if &engine=BASE %then %do;
83 %&mD.put NOTE: Retrieving BASE library path;
84 data _null_;
85 length up_uri $256 path cat_path $1024;
86 retain cat_path;
87 call missing (of _all_);
88 /* get all the filepaths of the UsingPackages association */
89 i=1;
90 rc3=metadata_getnasn("&liburi",'UsingPackages',i,up_uri);
91 do while (rc3>0);
92 /* get the DirectoryName attribute of the previous object */
93 rc4=metadata_getattr(up_uri,'DirectoryName',path);
94 if i=1 then path = '("'!!trim(path)!!'" ';
95 else path =' "'!!trim(path)!!'" ';
96 cat_path = trim(cat_path) !! " " !! trim(path) ;
97 i+1;
98 rc3=metadata_getnasn("&liburi",'UsingPackages',i,up_uri);
99 end;
100 cat_path = trim(cat_path) !! ")";
101 &mD.putlog "NOTE: Getting physical path for &libref library";
102 &mD.putlog rc3= up_uri= rc4= cat_path= path=;
103 &mD.putlog "NOTE: Libname cmd will be:";
104 &mD.putlog "libname &libref" cat_path;
105 call symputx("filepath",cat_path,'l');
106 run;
107
108 %if %sysevalf(&sysver<9.4) %then %do;
109 libname &libref &filepath;
110 %end;
111 %else %do;
112 /* apply the new filelocks option to cater for temporary locks */
113 libname &libref &filepath filelockwait=5;
114 %end;
115
116%end;
117%else %if &engine=REMOTE %then %do;
118 data x;
119 length rcCon rcProp rc k 3 uriCon uriProp PropertyValue PropertyName
120 Delimiter $256 properties $2048;
121 retain properties;
122 rcCon = metadata_getnasn("&liburi", "LibraryConnection", 1, uriCon);
123
124 rcProp = metadata_getnasn(uriCon, "Properties", 1, uriProp);
125
126 k = 1;
127 rcProp = metadata_getnasn(uriCon, "Properties", k, uriProp);
128 do while (rcProp > 0);
129 rc = metadata_getattr(uriProp , "DefaultValue",PropertyValue);
130 rc = metadata_getattr(uriProp , "PropertyName",PropertyName);
131 rc = metadata_getattr(uriProp , "Delimiter",Delimiter);
132 properties = trim(properties) !! " " !! trim(PropertyName)
133 !! trim(Delimiter) !! trim(PropertyValue);
134 output;
135 k+1;
136 rcProp = metadata_getnasn(uriCon, "Properties", k, uriProp);
137 end;
138 %&mD.put NOTE: Getting properties for REMOTE SHARE &libref library;
139 &mD.put _all_;
140 %&mD.put NOTE: Libname cmd will be:;
141 %&mD.put libname &libref &engine &properties slibref=&libref;
142 call symputx ("properties",trim(properties),'l');
143 run;
144
145 libname &libref &engine &properties slibref=&libref;
146
147%end;
148
149%else %if &engine=OLEDB %then %do;
150 %&mD.put NOTE: Retrieving OLEDB connection details;
151 data _null_;
152 length domain datasource provider properties schema
153 connx_uri domain_uri conprop_uri lib_uri schema_uri value $256.;
154 call missing (of _all_);
155 /* get source connection ID */
156 rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
157 /* get connection domain */
158 rc1=metadata_getnasn(connx_uri,'Domain',1,domain_uri);
159 rc2=metadata_getattr(domain_uri,'Name',domain);
160 &mD.putlog / 'NOTE: ' // 'NOTE- connection id: ' connx_uri ;
161 &mD.putlog 'NOTE- domain: ' domain;
162 /* get DSN and PROVIDER from connection properties */
163 i=0;
164 do until (rc<0);
165 i+1;
166 rc=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
167 rc2=metadata_getattr(conprop_uri,'Name',value);
168 if value='Connection.OLE.Property.DATASOURCE.Name.xmlKey.txt' then do;
169 rc3=metadata_getattr(conprop_uri,'DefaultValue',datasource);
170 end;
171 else if value='Connection.OLE.Property.PROVIDER.Name.xmlKey.txt' then do;
172 rc4=metadata_getattr(conprop_uri,'DefaultValue',provider);
173 end;
174 else if value='Connection.OLE.Property.PROPERTIES.Name.xmlKey.txt' then
175 do;
176 rc5=metadata_getattr(conprop_uri,'DefaultValue',properties);
177 end;
178 end;
179 &mD.putlog 'NOTE- dsn/provider/properties: ' /
180 datasource provider properties;
181 &mD.putlog 'NOTE- schema: ' schema // 'NOTE-';
182
183 /* get SCHEMA */
184 rc6=metadata_getnasn("&liburi",'UsingPackages',1,lib_uri);
185 rc7=metadata_getattr(lib_uri,'SchemaName',schema);
186 call symputx('SQL_domain',domain,'l');
187 call symputx('SQL_dsn',datasource,'l');
188 call symputx('SQL_provider',provider,'l');
189 call symputx('SQL_properties',properties,'l');
190 call symputx('SQL_schema',schema,'l');
191 run;
192
193 %if %length(&open_passthrough)>0 %then %do;
194 proc sql &sql_options;
195 connect to OLEDB as &open_passthrough(INSERT_SQL=YES
196 /* need additional properties to make this work */
197 properties=('Integrated Security'=SSPI
198 'Persist Security Info'=True
199 %sysfunc(compress(%str(&SQL_properties),%str(())))
200 )
201 DATASOURCE=&sql_dsn PROMPT=NO
202 PROVIDER=&sql_provider SCHEMA=&sql_schema CONNECTION = GLOBAL);
203 %end;
204 %else %do;
205 LIBNAME &libref OLEDB PROPERTIES=&sql_properties
206 DATASOURCE=&sql_dsn PROVIDER=&sql_provider SCHEMA=&sql_schema
207 %if %length(&sql_domain)>0 %then %do;
208 authdomain="&sql_domain"
209 %end;
210 connection=shared;
211 %end;
212%end;
213%else %if &engine=ODBC %then %do;
214 %&mD.put NOTE: Retrieving ODBC connection details;
215 data _null_;
216 length connx_uri conprop_uri value datasource up_uri schema domprop_uri
217 authdomain $256.;
218 call missing (of _all_);
219 /* get source connection ID */
220 rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
221 /* get connection properties */
222 i=0;
223 do until (rc2<0);
224 i+1;
225 rc2=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
226 rc3=metadata_getattr(conprop_uri,'Name',value);
227 if value='Connection.ODBC.Property.DATASRC.Name.xmlKey.txt' then do;
228 rc4=metadata_getattr(conprop_uri,'DefaultValue',datasource);
229 rc2=-1;
230 end;
231 end;
232
233 /* get auth domain */
234 autrc=metadata_getnasn(connx_uri,"Domain",1,domprop_uri);
235 arc=metadata_getattr(domprop_uri,"Name",authdomain);
236 if not missing(authdomain) then authdomain=cats('AUTHDOMAIN=',authdomain);
237 call symputx('authdomain',authdomain,'l');
238
239 /* get SCHEMA */
240 rc6=metadata_getnasn("&liburi",'UsingPackages',1,up_uri);
241 rc7=metadata_getattr(up_uri,'SchemaName',schema);
242 &mD.put rc= connx_uri= rc2= conprop_uri= rc3= value= rc4= datasource=
243 rc6= up_uri= rc7= schema=;
244
245 call symputx('SQL_schema',schema,'l');
246 call symputx('SQL_dsn',datasource,'l');
247 run;
248
249 %if %length(&open_passthrough)>0 %then %do;
250 proc sql &sql_options;
251 connect to ODBC as &open_passthrough
252 (INSERT_SQL=YES DATASRC=&sql_dsn. CONNECTION=global);
253 %end;
254 %else %do;
255 libname &libref ODBC DATASRC=&sql_dsn SCHEMA=&sql_schema &authdomain;
256 %end;
257%end;
258%else %if &engine=POSTGRES %then %do;
259 %put NOTE: Obtaining POSTGRES library details;
260 data _null_;
261 length database ignore_read_only_columns direct_exe preserve_col_names
262 preserve_tab_names server schema authdomain user password
263 prop name value uri urisrc $256.;
264 call missing (of _all_);
265 /* get database value */
266 prop='Connection.DBMS.Property.DB.Name.xmlKey.txt';
267 rc=metadata_getprop("&liburi",prop,database,"");
268 if database^='' then database='database='!!quote(trim(database));
269 call symputx('database',database,'l');
270
271 /* get IGNORE_READ_ONLY_COLUMNS value */
272 prop='Library.DBMS.Property.DBIROC.Name.xmlKey.txt';
273 rc=metadata_getprop("&liburi",prop,ignore_read_only_columns,"");
274 if ignore_read_only_columns^='' then ignore_read_only_columns=
275 'ignore_read_only_columns='!!ignore_read_only_columns;
276 call symputx('ignore_read_only_columns',ignore_read_only_columns,'l');
277
278 /* get DIRECT_EXE value */
279 prop='Library.DBMS.Property.DirectExe.Name.xmlKey.txt';
280 rc=metadata_getprop("&liburi",prop,direct_exe,"");
281 if direct_exe^='' then direct_exe='direct_exe='!!direct_exe;
282 call symputx('direct_exe',direct_exe,'l');
283
284 /* get PRESERVE_COL_NAMES value */
285 prop='Library.DBMS.Property.PreserveColNames.Name.xmlKey.txt';
286 rc=metadata_getprop("&liburi",prop,preserve_col_names,"");
287 if preserve_col_names^='' then preserve_col_names=
288 'preserve_col_names='!!preserve_col_names;
289 call symputx('preserve_col_names',preserve_col_names,'l');
290
291 /* get PRESERVE_TAB_NAMES value */
292 /* be careful with PRESERVE_TAB_NAMES=YES - it will mean your table will
293 become case sensitive!! */
294 prop='Library.DBMS.Property.PreserveTabNames.Name.xmlKey.txt';
295 rc=metadata_getprop("&liburi",prop,preserve_tab_names,"");
296 if preserve_tab_names^='' then preserve_tab_names=
297 'preserve_tab_names='!!preserve_tab_names;
298 call symputx('preserve_tab_names',preserve_tab_names,'l');
299
300 /* get SERVER value */
301 if metadata_getnasn("&liburi","LibraryConnection",1,uri)>0 then do;
302 prop='Connection.DBMS.Property.SERVER.Name.xmlKey.txt';
303 rc=metadata_getprop(uri,prop,server,"");
304 end;
305 if server^='' then server='server='!!quote(cats(server));
306 call symputx('server',server,'l');
307
308 /* get SCHEMA value */
309 if metadata_getnasn("&liburi","UsingPackages",1,uri)>0 then do;
310 rc=metadata_getattr(uri,"SchemaName",schema);
311 end;
312 if schema^='' then schema='schema='!!schema;
313 call symputx('schema',schema,'l');
314
315 /* get AUTHDOMAIN value */
316 /* this is only useful if the user account contains that auth domain
317 if metadata_getnasn("&liburi","DefaultLogin",1,uri)>0 then do;
318 rc=metadata_getnasn(uri,"Domain",1,urisrc);
319 rc=metadata_getattr(urisrc,"Name",authdomain);
320 end;
321 if authdomain^='' then authdomain='authdomain='!!quote(trim(authdomain));
322 */
323 call symputx('authdomain',authdomain,'l');
324
325 /* get user & pass */
326 if authdomain='' & metadata_getnasn("&liburi","DefaultLogin",1,uri)>0 then
327 do;
328 rc=metadata_getattr(uri,"UserID",user);
329 rc=metadata_getattr(uri,"Password",password);
330 end;
331 if user^='' then do;
332 user='user='!!quote(trim(user));
333 password='password='!!quote(trim(password));
334 end;
335 call symputx('user',user,'l');
336 call symputx('password',password,'l');
337
338 &md.put _all_;
339 run;
340
341 %if %length(&open_passthrough)>0 %then %do;
342 %put %str(WARN)ING: Passthrough option for postgres not yet supported;
343 %return;
344 %end;
345 %else %do;
346 %if &mdebug=1 %then %do;
347 %put NOTE: Executing the following:/;
348 %put NOTE- libname &libref POSTGRES &database &ignore_read_only_columns;
349 %put NOTE- &direct_exe &preserve_col_names &preserve_tab_names;
350 %put NOTE- &server &schema &authdomain &user &password //;
351 %end;
352 libname &libref POSTGRES &database &ignore_read_only_columns &direct_exe
353 &preserve_col_names &preserve_tab_names &server &schema &authdomain
354 &user &password;
355 %end;
356%end;
357%else %if &engine=ORACLE %then %do;
358 %put NOTE: Obtaining &engine library details;
359 data _null_;
360 length assocuri1 assocuri2 assocuri3 authdomain path schema $256;
361 call missing (of _all_);
362
363 /* get auth domain */
364 rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri1);
365 rc=metadata_getnasn(assocuri1,'Domain',1,assocuri2);
366 rc=metadata_getattr(assocuri2,"Name",authdomain);
367 call symputx('authdomain',authdomain,'l');
368
369 /* path */
370 rc=metadata_getprop(assocuri1,
371 'Connection.Oracle.Property.PATH.Name.xmlKey.txt',path);
372 call symputx('path',path,'l');
373
374 /* schema */
375 rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
376 rc=metadata_getattr(assocuri3,'SchemaName',schema);
377 call symputx('schema',schema,'l');
378 run;
379 %put NOTE: Executing the following:/; %put NOTE-;
380 %put NOTE- libname &libref ORACLE path=&path schema=&schema;
381 %put NOTE- authdomain=&authdomain;
382 %put NOTE-;
383 libname &libref ORACLE path=&path schema=&schema authdomain=&authdomain;
384%end;
385%else %if &engine=SQLSVR %then %do;
386 %put NOTE: Obtaining &engine library details;
387 data _null;
388 length assocuri1 assocuri2 assocuri3 authdomain path schema userid
389 passwd $256;
390 call missing (of _all_);
391
392 rc=metadata_getnasn("&liburi",'DefaultLogin',1,assocuri1);
393 rc=metadata_getattr(assocuri1,"UserID",userid);
394 rc=metadata_getattr(assocuri1,"Password",passwd);
395 call symputx('user',userid,'l');
396 call symputx('pass',passwd,'l');
397
398 /* path */
399 rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri2);
400 rc=metadata_getprop(assocuri2,
401 'Connection.SQL.Property.Datasrc.Name.xmlKey.txt',path);
402 call symputx('path',path,'l');
403
404 /* schema */
405 rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
406 rc=metadata_getattr(assocuri3,'SchemaName',schema);
407 call symputx('schema',schema,'l');
408 run;
409
410 %put NOTE: Executing the following:/; %put NOTE-;
411 %put NOTE- libname &libref SQLSVR datasrc=&path schema=&schema ;
412 %put NOTE- user="&user" pass="XXX";
413 %put NOTE-;
414
415 libname &libref SQLSVR datasrc=&path schema=&schema user="&user" pass="&pass";
416%end;
417%else %if &engine=SASIOSNF or &engine=SNOW %then %do;
418 %&mD.put NOTE: Retrieving SNOW connection details;
419 data _null_;
420 length connx_uri conprop_uri value server up_uri schema domprop_uri
421 authdomain database $256.;
422 call missing (of _all_);
423 /* get source connection ID */
424 rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
425 /* get connection properties */
426 i=0;
427 do until (rc2<0);
428 i+1;
429 rc2=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
430 rc3=metadata_getattr(conprop_uri,'Name',value);
431 if value='Connection.DBMS.Property.SERVER.Name.xmlKey.txt' then do;
432 rc4=metadata_getattr(conprop_uri,'DefaultValue',server);
433 rc2=-1;
434 end;
435 end;
436
437 /* get auth domain */
438 autrc=metadata_getnasn(connx_uri,"Domain",1,domprop_uri);
439 arc=metadata_getattr(domprop_uri,"Name",authdomain);
440 if not missing(authdomain) then authdomain=cats('AUTHDOMAIN=',authdomain);
441 call symputx('authdomain',authdomain,'l');
442
443 /* get SCHEMA */
444 rc6=metadata_getnasn("&liburi",'UsingPackages',1,up_uri);
445 rc7=metadata_getattr(up_uri,'SchemaName',schema);
446 &mD.put rc= connx_uri= rc2= conprop_uri= rc3= value= rc4= server=
447 rc6= up_uri= rc7= schema=;
448
449 /* get database value */
450 prop='Connection.DBMS.Property.DB.Name.xmlKey.txt';
451 rc=metadata_getprop("&liburi",prop,database,"");
452 if database^='' then database='database='!!quote(trim(database));
453 call symputx('database',database,'l');
454
455 call symputx('snow_schema',schema,'l');
456 call symputx('snow_server',server,'l');
457 run;
458
459 libname &libref SNOW SERVER="&snow_server" SCHEMA=&snow_schema &authdomain
460 &database;
461 %if %length(&open_passthrough)>0 %then %do;
462 proc sql;
463 connect using &libref as &open_passthrough;
464 %end;
465%end;
466%else %if &engine=TERADATA %then %do;
467 %put NOTE: Obtaining &engine library details;
468 data _null;
469 length assocuri1 assocuri2 assocuri3 authdomain path schema userid
470 passwd $256;
471 call missing (of _all_);
472
473 /* get auth domain */
474 rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri1);
475 rc=metadata_getnasn(assocuri1,'Domain',1,assocuri2);
476 rc=metadata_getattr(assocuri2,"Name",authdomain);
477 call symputx('authdomain',authdomain,'l');
478
479 /*
480 rc=metadata_getnasn("&liburi",'DefaultLogin',1,assocuri1);
481 rc=metadata_getattr(assocuri1,"UserID",userid);
482 rc=metadata_getattr(assocuri1,"Password",passwd);
483 call symputx('user',userid,'l');
484 call symputx('pass',passwd,'l');
485 */
486
487 /* path */
488 rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri2);
489 rc=metadata_getprop(assocuri2,
490 'Connection.Teradata.Property.SERVER.Name.xmlKey.txt',path);
491 call symputx('path',path,'l');
492
493 /* schema */
494 rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
495 rc=metadata_getattr(assocuri3,'SchemaName',schema);
496 call symputx('schema',schema,'l');
497 run;
498
499 %put NOTE: Executing the following:/; %put NOTE-;
500 %put NOTE- libname &libref TERADATA server="&path" schema=&schema ;
501 %put NOTe- authdomain=&authdomain;
502 %put NOTE-;
503
504 libname &libref TERADATA server="&path" schema=&schema authdomain=&authdomain;
505%end;
506%else %if &engine= %then %do;
507 %put NOTE: Libref &libref is not registered in metadata;
508 %&mAbort.mp_abort(
509 msg=%str(ERR)OR: Libref &libref is not registered in metadata
510 ,mac=mm_assigndirectlib.sas);
511 %return;
512%end;
513%else %do;
514 %put %str(WARN)ING: Engine &engine is currently unsupported;
515 %put %str(WARN)ING- Please contact your support team.;
516 %return;
517%end;
518
519%mend mm_assigndirectlib;