/*-------------------<-- Start of Description-->---------------------\
| Fast Retrieve Data from Device Database, very fast, more than 8    |
| times faster than the regular query;                               |
|---------------------<-- End of Description-->----------------------|
|--------------------------------------------------------------------|
|------------<-- Start of Files or Arguments Needed-->---------------|
| libname: a defined database library name;                          |
| dbtable: the table name in the database;                           |
| where: the where condition, if you want to select only a subset of |
|        all the entire table;                                       |
| dbkey: the key of the database table;                              |
| orderby: sort the output data by;                                  |
| schema: the schema, such as the "acedaf$current" for table         |
|         "acedaf$current.pat_dvn" in the OC;                        |
| outdata: the output dataset created;                               |
|-------------<-- End of Files or Arguments Needed-->----------------|
|--------------------------------------------------------------------|
|------------------<-- Start of Files Created-->---------------------|
| Example:                                                           |
| %macro sqlconcat(indata=, var=, outvar=);                          |
| %global &outvar;                                                   |
| %local localv1;                                                    |
| proc sql noprint;                                                  |
|   select distinct &var                                             |
|   into :localv1 separated by '", "'                                |
|   from &indata                                                     |
|   where &var ne ' '                                                |
|   order by &var.;                                                  |
| quit;                                                              |
| %let localv1="&localv1";                                           |
| %let &outvar=(&localv1);                                           |
| %put &outvar is &&&outvar;                                         |
| %mend sqlconcat;                                                   |
| %sqlconcat(indata=acedcrf.implant, var=serialno,                   |
|                                              outvar=acedallserial);|
| libname DVdbms odbc dsn='device' user=dr password=dr               |
|                          access=readonly connection=GLOBALREAD;    |
| %query(libname=DVdbms, dbtable=episode STD_PARAM_PACING,         |
|        where=(comp_sernum in &acedallserial), dbkey=comp_sernum,   |
|        orderby=comp_sernum, outdata=episode STD_PARAM_PACING);     |
\-------------------<-- End of Files Created-->---------------------*/
%macro query(libname=,dbtable=,where=,dbkey=,schema=,orderby=,outdata=&dbtable);
/*--------------------------------------------\
| Copy Right: Duo Zhou;                       |
| Created: 10-11-2001 8:43pm;                 |
| Purpose: Retrieve a table from a Database;  |
\--------------------------------------------*/
%local ndsns localvname _i_ _j_;
%let dblib=%sysfunc(dequote(&libname));
%if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&orderby))))), %str(%()) eq 1) and
    (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&orderby))))))), %str(%))) eq 1) %then
   %let orderby=%substr(%quote(%trim(%quote(%left(%quote(&orderby))))), 2, %eval(%length(%trim(%quote(%left(%quote(&orderby)))))-2));
%let ndsns=1;
%let dbtab1=%qscan(&dbtable, &ndsns, %str( ));
%let data1=%qscan(&outdata, &ndsns, %str( ));
%do %while(%length(&&dbtab&ndsns) gt 0);
   %let ndsns=%eval(&ndsns+1);
     %let dbtab&ndsns=%qscan(&dbtable, &ndsns, %str( ));
   %let data&ndsns=%qscan(&outdata,&ndsns,%str( ));
   %if &&data&ndsns eq %then %let data&ndsns=&&dbtab&ndsns;
%end;
%let ndsns =%eval(&ndsns-1);
%if (%quote(&schema) ne) %then %let schema="%trim(%nrbquote(%left(%nrbquote(%sysfunc(dequote(&schema))))))";
%do _i_=1 %to &ndsns;
   proc sql;
      Create table &&data&_i_ as
      Select *
      from &dblib..&&dbtab&_i_ 
           %if (%quote(&dbkey) ne) or (%quote(&schema) ne) %then %do;
              (%if (%quote(&dbkey) > 0) %then %do; dbkey=&dbkey %end;
               %if (%quote(&schema) ne) %then %do;  schema=&schema %end;)
           %end;
      %if (%length(&where) > 0) %then %do; where &where %end;
      %if (%length(&orderby)> 0) %then %do; order by &orderby %end;;
   quit;
%end;
%mend query;