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