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