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 [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 %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;
68 data _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');
78 run;
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 authdomain $256.;
216  call missing (of _all_);
217  /* get source connection ID */
218  rc=metadata_getnasn("&liburi",'LibraryConnection',1,connx_uri);
219  /* get connection properties */
220  i=0;
221  do until (rc2<0);
222  i+1;
223  rc2=metadata_getnasn(connx_uri,'Properties',i,conprop_uri);
224  rc3=metadata_getattr(conprop_uri,'Name',value);
225  if value='Connection.ODBC.Property.DATASRC.Name.xmlKey.txt' then do;
226  rc4=metadata_getattr(conprop_uri,'DefaultValue',datasource);
227  rc2=-1;
228  end;
229  end;
230 
231  /* get auth domain */
232  autrc=metadata_getnasn(connx_uri,"Domain",1,domprop_uri);
233  arc=metadata_getattr(domprop_uri,"Name",authdomain);
234  if not missing(authdomain) then authdomain=cats('AUTHDOMAIN=',authdomain);
235  call symputx('authdomain',authdomain,'l');
236 
237  /* get SCHEMA */
238  rc6=metadata_getnasn("&liburi",'UsingPackages',1,up_uri);
239  rc7=metadata_getattr(up_uri,'SchemaName',schema);
240  &mD.put rc= connx_uri= rc2= conprop_uri= rc3= value= rc4= datasource=
241  rc6= up_uri= rc7= schema=;
242 
243  call symputx('SQL_schema',schema,'l');
244  call symputx('SQL_dsn',datasource,'l');
245  run;
246 
247  %if %length(&open_passthrough)>0 %then %do;
248  proc sql &sql_options;
249  connect to ODBC as &open_passthrough
250  (INSERT_SQL=YES DATASRC=&sql_dsn. CONNECTION=global);
251  %end;
252  %else %do;
253  libname &libref ODBC DATASRC=&sql_dsn SCHEMA=&sql_schema &authdomain;
254  %end;
255 %end;
256 %else %if &engine=POSTGRES %then %do;
257  %put NOTE: Obtaining POSTGRES library details;
258  data _null_;
259  length database ignore_read_only_columns direct_exe preserve_col_names
260  preserve_tab_names server schema authdomain user password
261  prop name value uri urisrc $256.;
262  call missing (of _all_);
263  /* get database value */
264  prop='Connection.DBMS.Property.DB.Name.xmlKey.txt';
265  rc=metadata_getprop("&liburi",prop,database,"");
266  if database^='' then database='database='!!quote(trim(database));
267  call symputx('database',database,'l');
268 
269  /* get IGNORE_READ_ONLY_COLUMNS value */
270  prop='Library.DBMS.Property.DBIROC.Name.xmlKey.txt';
271  rc=metadata_getprop("&liburi",prop,ignore_read_only_columns,"");
272  if ignore_read_only_columns^='' then ignore_read_only_columns=
273  'ignore_read_only_columns='!!ignore_read_only_columns;
274  call symputx('ignore_read_only_columns',ignore_read_only_columns,'l');
275 
276  /* get DIRECT_EXE value */
277  prop='Library.DBMS.Property.DirectExe.Name.xmlKey.txt';
278  rc=metadata_getprop("&liburi",prop,direct_exe,"");
279  if direct_exe^='' then direct_exe='direct_exe='!!direct_exe;
280  call symputx('direct_exe',direct_exe,'l');
281 
282  /* get PRESERVE_COL_NAMES value */
283  prop='Library.DBMS.Property.PreserveColNames.Name.xmlKey.txt';
284  rc=metadata_getprop("&liburi",prop,preserve_col_names,"");
285  if preserve_col_names^='' then preserve_col_names=
286  'preserve_col_names='!!preserve_col_names;
287  call symputx('preserve_col_names',preserve_col_names,'l');
288 
289  /* get PRESERVE_TAB_NAMES value */
290  /* be careful with PRESERVE_TAB_NAMES=YES - it will mean your table will
291  become case sensitive!! */
292  prop='Library.DBMS.Property.PreserveTabNames.Name.xmlKey.txt';
293  rc=metadata_getprop("&liburi",prop,preserve_tab_names,"");
294  if preserve_tab_names^='' then preserve_tab_names=
295  'preserve_tab_names='!!preserve_tab_names;
296  call symputx('preserve_tab_names',preserve_tab_names,'l');
297 
298  /* get SERVER value */
299  if metadata_getnasn("&liburi","LibraryConnection",1,uri)>0 then do;
300  prop='Connection.DBMS.Property.SERVER.Name.xmlKey.txt';
301  rc=metadata_getprop(uri,prop,server,"");
302  end;
303  if server^='' then server='server='!!quote(cats(server));
304  call symputx('server',server,'l');
305 
306  /* get SCHEMA value */
307  if metadata_getnasn("&liburi","UsingPackages",1,uri)>0 then do;
308  rc=metadata_getattr(uri,"SchemaName",schema);
309  end;
310  if schema^='' then schema='schema='!!schema;
311  call symputx('schema',schema,'l');
312 
313  /* get AUTHDOMAIN value */
314  /* this is only useful if the user account contains that auth domain
315  if metadata_getnasn("&liburi","DefaultLogin",1,uri)>0 then do;
316  rc=metadata_getnasn(uri,"Domain",1,urisrc);
317  rc=metadata_getattr(urisrc,"Name",authdomain);
318  end;
319  if authdomain^='' then authdomain='authdomain='!!quote(trim(authdomain));
320  */
321  call symputx('authdomain',authdomain,'l');
322 
323  /* get user & pass */
324  if authdomain='' & metadata_getnasn("&liburi","DefaultLogin",1,uri)>0 then
325  do;
326  rc=metadata_getattr(uri,"UserID",user);
327  rc=metadata_getattr(uri,"Password",password);
328  end;
329  if user^='' then do;
330  user='user='!!quote(trim(user));
331  password='password='!!quote(trim(password));
332  end;
333  call symputx('user',user,'l');
334  call symputx('password',password,'l');
335 
336  &md.put _all_;
337  run;
338 
339  %if %length(&open_passthrough)>0 %then %do;
340  %put %str(WARN)ING: Passthrough option for postgres not yet supported;
341  %return;
342  %end;
343  %else %do;
344  %if &mdebug=1 %then %do;
345  %put NOTE: Executing the following:/;
346  %put NOTE- libname &libref POSTGRES &database &ignore_read_only_columns;
347  %put NOTE- &direct_exe &preserve_col_names &preserve_tab_names;
348  %put NOTE- &server &schema &authdomain &user &password //;
349  %end;
350  libname &libref POSTGRES &database &ignore_read_only_columns &direct_exe
351  &preserve_col_names &preserve_tab_names &server &schema &authdomain
352  &user &password;
353  %end;
354 %end;
355 %else %if &engine=ORACLE %then %do;
356  %put NOTE: Obtaining &engine library details;
357  data _null_;
358  length assocuri1 assocuri2 assocuri3 authdomain path schema $256;
359  call missing (of _all_);
360 
361  /* get auth domain */
362  rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri1);
363  rc=metadata_getnasn(assocuri1,'Domain',1,assocuri2);
364  rc=metadata_getattr(assocuri2,"Name",authdomain);
365  call symputx('authdomain',authdomain,'l');
366 
367  /* path */
368  rc=metadata_getprop(assocuri1,
369  'Connection.Oracle.Property.PATH.Name.xmlKey.txt',path);
370  call symputx('path',path,'l');
371 
372  /* schema */
373  rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
374  rc=metadata_getattr(assocuri3,'SchemaName',schema);
375  call symputx('schema',schema,'l');
376  run;
377  %put NOTE: Executing the following:/; %put NOTE-;
378  %put NOTE- libname &libref ORACLE path=&path schema=&schema;
379  %put NOTE- authdomain=&authdomain;
380  %put NOTE-;
381  libname &libref ORACLE path=&path schema=&schema authdomain=&authdomain;
382 %end;
383 %else %if &engine=SQLSVR %then %do;
384  %put NOTE: Obtaining &engine library details;
385  data _null;
386  length assocuri1 assocuri2 assocuri3 authdomain path schema userid
387  passwd $256;
388  call missing (of _all_);
389 
390  rc=metadata_getnasn("&liburi",'DefaultLogin',1,assocuri1);
391  rc=metadata_getattr(assocuri1,"UserID",userid);
392  rc=metadata_getattr(assocuri1,"Password",passwd);
393  call symputx('user',userid,'l');
394  call symputx('pass',passwd,'l');
395 
396  /* path */
397  rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri2);
398  rc=metadata_getprop(assocuri2,
399  'Connection.SQL.Property.Datasrc.Name.xmlKey.txt',path);
400  call symputx('path',path,'l');
401 
402  /* schema */
403  rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
404  rc=metadata_getattr(assocuri3,'SchemaName',schema);
405  call symputx('schema',schema,'l');
406  run;
407 
408  %put NOTE: Executing the following:/; %put NOTE-;
409  %put NOTE- libname &libref SQLSVR datasrc=&path schema=&schema ;
410  %put NOTE- user="&user" pass="XXX";
411  %put NOTE-;
412 
413  libname &libref SQLSVR datasrc=&path schema=&schema user="&user" pass="&pass";
414 %end;
415 %else %if &engine=TERADATA %then %do;
416  %put NOTE: Obtaining &engine library details;
417  data _null;
418  length assocuri1 assocuri2 assocuri3 authdomain path schema userid
419  passwd $256;
420  call missing (of _all_);
421 
422  /* get auth domain */
423  rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri1);
424  rc=metadata_getnasn(assocuri1,'Domain',1,assocuri2);
425  rc=metadata_getattr(assocuri2,"Name",authdomain);
426  call symputx('authdomain',authdomain,'l');
427 
428  /*
429  rc=metadata_getnasn("&liburi",'DefaultLogin',1,assocuri1);
430  rc=metadata_getattr(assocuri1,"UserID",userid);
431  rc=metadata_getattr(assocuri1,"Password",passwd);
432  call symputx('user',userid,'l');
433  call symputx('pass',passwd,'l');
434  */
435 
436  /* path */
437  rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri2);
438  rc=metadata_getprop(assocuri2,
439  'Connection.Teradata.Property.SERVER.Name.xmlKey.txt',path);
440  call symputx('path',path,'l');
441 
442  /* schema */
443  rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
444  rc=metadata_getattr(assocuri3,'SchemaName',schema);
445  call symputx('schema',schema,'l');
446  run;
447 
448  %put NOTE: Executing the following:/; %put NOTE-;
449  %put NOTE- libname &libref TERADATA server="&path" schema=&schema ;
450  %put NOTe- authdomain=&authdomain;
451  %put NOTE-;
452 
453  libname &libref TERADATA server="&path" schema=&schema authdomain=&authdomain;
454 %end;
455 %else %if &engine= %then %do;
456  %put NOTE: Libref &libref is not registered in metadata;
457  %&mAbort.mp_abort(
458  msg=%str(ERR)OR: Libref &libref is not registered in metadata
459  ,mac=mm_assigndirectlib.sas);
460  %return;
461 %end;
462 %else %do;
463  %put %str(WARN)ING: Engine &engine is currently unsupported;
464  %put %str(WARN)ING- Please contact your support team.;
465  %return;
466 %end;
467 
468 %mend mm_assigndirectlib;