SAS ODBC与本地库中的表格

Question

如果我在SAS中使用ODBC运行创建表，该怎么办？此表现在保存到SAS的永久库中。现在我想采取该表，因为它搜索了数百万行的数据，并在完成后，我筛选了这些项目，并且此表具有664个不同的sys_id行。

我需要采取这664个不同的sys_id行，现在我需要拉出匹配的ODBC中的所有sys_id。我正在寻找匹配在一段时间内有医疗索赔的任何sys_id。我知道如何执行查询部分，但不知道如何同时连接本地库和ODBC中的表。我已经尝试过将诸如libname test.sys_id之类的各种东西放在galaxy.sys_id中，但没有任何效果。如果甚至可能的话，我也试着阅读。我想这可能是不可能的。奇怪的是我可以在Access中做到这一点，将我创建的表连接到服务器上的表中，这样我就可以使用SAS。我无法在Access中运行此程序。内存不足。有什么建议？

下面是到目前为止，我已经试过代码：

/***the table is successfully created and saved to my libname readm*****/ 
proc sql; 
connect to odbc (dsn=server user=user password=password); 
create table readm.test as 
select * from connection to odbc 
    (select distinct server.sys_id, server.clm_aud_nbr, 
        server.fst_srvc_dt, server.proc_cd 
    from server.table 
    where server.proc_cd in ('27130', '27132', '27447') 
     and server.fst_srvc_dt between (&startdt) and (&enddt)) 
order by server.sys_id, server.fst_srvc_dt; 
disconnect from odbc; 
quit; 

proc sql; 
connect to odbc (dsn=server user=user password=password); 
create table readm.test2 as 
select * from connection to odbc 
    (select libname readm.test, 
     server.mem_sys_id, server.clm_aud_nbr, server.fst_srvc_dt, 
     server.proc_cd 
    from libname readm.test 
    left outer join server.table on 
    readm.test_sys_id = server.table_sys_id 
    where server.fst_srvc_dt 
    between (&startdt) ad (&enddt)) 
    disconnect from odbc; 
    quit; 

Answer 1

既然你能够做到这一点，你要EXCEPT从本地机器上使用ODBC数据加入一个表的一切，这似乎是一个子查询会工作。

一旦你的子查询得到664个sys_ids，即小的子集与ODBC数据连接只返回所需的记录......这不应该是记录

PROC SQL子查询链接Here和Here的unreasoable量

如果您熟悉SAS中的HASH对象......这与此类似。或者以前，使用Proc SQL创建一个宏变量，其中所有的sys_id用逗号分隔，并在数据步骤中与IN运算符一起使用（如@Rob Penridge在宏中使用的）。

Answer 2

优秀的问题...我们有一个宏，我们用这里来解决这个问题，因为我们不需要上传文件到ODBC服务器或创建临时表等等的能力......一个简单的例子宏是：

proc sql noprint; 
    create table xx as 
    select * 
    from sashelp.class 
    where name in ( %ds2list(iDs=sashelp.class, iField=name, iQuote=1, iDelimiter=%str(,)) ) 
    ; 
quit; 

尽管上面的示例不使用ODBC直通，它将正常工作。如果OPTION MPRINT上则日志会显示类似下面：

121 proc sql noprint; 
122 create table xx as 
123 select * 
124 from sashelp.class 
125 where name in (%ds2list(iDs=sashelp.class,iField=name,iQuote=1, iDelimiter=%str(,))) 
MPRINT(DS2LIST): 'Alfred' 
MPRINT(DS2LIST): ,'Alice' 
MPRINT(DS2LIST): ,'Barbara' 
MPRINT(DS2LIST): ,'Carol' 
MPRINT(DS2LIST): ,'Henry' 
MPRINT(DS2LIST): ,'James' 
MPRINT(DS2LIST): ,'Jane' 
MPRINT(DS2LIST): ,'Janet' 
MPRINT(DS2LIST): ,'Jeffrey' 
MPRINT(DS2LIST): ,'John' 
MPRINT(DS2LIST): ,'Joyce' 
MPRINT(DS2LIST): ,'Judy' 
MPRINT(DS2LIST): ,'Louise' 
MPRINT(DS2LIST): ,'Mary' 
MPRINT(DS2LIST): ,'Philip' 
MPRINT(DS2LIST): ,'Robert' 
MPRINT(DS2LIST): ,'Ronald' 
MPRINT(DS2LIST): ,'Thomas' 
MPRINT(DS2LIST): ,'William' 
126 ; 
127 quit; 
NOTE: Table WORK.XX created, with 19 rows and 5 columns. 
NOTE: PROCEDURE SQL used (Total process time): 
     real time   0.15 seconds 
     cpu time   0.06 seconds 

正如你可以看到它产生一个逗号分隔的名字列表，并引述的名字。您可以更改分隔符以及使用的引号。列表中的项目数量没有限制（我们在超过10万个项目的列表中使用它），因为列表是由宏“流”的，而不是存储在宏变量中。唯一的大小限制是由ODBC服务器的querysize执行的。宏的代码有点可怕，但将其放在宏自动调用文件夹中，并忘记它。

宏代码如下：

/*************************************************************************** 
** PROGRAM: MACRO.DS2LIST.SAS 
** 
** UTILITY PROGRAM THAT DETECTS RETURNS A LIST OF FIELD VALUES FROM A 
** DATASET IN DELIMITED FORMAT. 
** 
** PARAMETERS: 
** iDs  : THE LIBNAME.DATASET NAME THAT YOU WANT TO CHECK. 
** iField : THE FIELD THAT CONTAINS THE VALUES YOU WANT RETURNED IN A 
**    DELIMITED FORMAT. 
** iDelimiter: DEFAULT IS A COMMA. THE DELIMITER TO USE FOR THE RETURNED LIST. 
** iDsOptions: ANY STANDARD DATASET OPTIONS THAT YOU WOULD LIKE TO APPLY SUCH 
**    AS A WHERE STATEMENT. 
** iQuote : (0=NO,1=YES). DEFAULT=0/NO. DETERMINES WHETHER THE RETURNED 
**    LIST IS QUOTED OR NOT. 
** iQuoteChar: (SINGLE,DOUBLE) DEFAULT=SINGLE. SPECIFIES WHETHER SINGLE0. 
**    OR DOUBLE QUOTES ARE USED WHEN QUOTING THE RETURNED LIST 
** 
***************************************************************************** 
** VERSION: 
** 
** 1.0 ON: 05-FEB-2007 BY: ROBERT PENRIDGE 
**  CREATED. 
** 1.1 ON: 29-APR-2008 BY: ROBERT PENRIDGE 
**  PUT IN ERROR CHECKING. 
**  ADDED AUTOMATIC TYPE DETECTION 
**  FIXED OUTPUT. 
** 1.2 ON: 23-APR-2010 BY: ROBERT PENRIDGE 
**  CHANGED SO THAT OUTPUT SPOOLED. ALLOWS MACRO TO RETURN OUTPUT > 64KB. 
** 1.3 ON: 12-MAY-2010 BY: ROBERT PENRIDGE 
**  ADDED PARAMETER CHECK AFTER I SPENT 10 MINUTES TRYING TO FIGURE OUT 
**  WHY MY CODE WAS RETURNING AN ERROR. DUH! 
** 1.4 ON: 26-MAY-2010 BY: KN 
**  ADDED IQUOTE. 
** 1.5 ON: 08-JUN-2010 BY: RP 
**  FIXED DCLOSE SO DATASET WOULD CLOSE PROPERLY AND RELEASE LOCK. 
** 1.6 ON: 16-JUN-2010 BY: RP 
**  ADDED IQUOTECHAR PARAMETER 
** 1.7 ON: 20-JUL-2010 BY: RP 
**  UNQUOTED RETURNED VALUES 
** 1.8 ON: 11-OCT-2010 BY: KN 
**  MODIFIED TO ALLOW BLANK CHARACTER VALUES AND ALSO REMOVED TRAILING 
**  MODIFIED TO ALLOW PARENTHESES IN CHARACTER VALUES 
*****************************************************************************/ 

%macro ds2list(iDs=, iField=, iDsOptions=, iDelimiter=%str(,), iQuote=0, iQuoteChar=single); 
    %local dsid pos rc result cnt quotechar; 

    %let result=; 
    %let cnt=0; 

    %if &iQuote %then %do; 
    %if "%upcase(&iQuoteChar)" eq "DOUBLE" %then %do; 
     %let quotechar = %nrstr(%"); 
    %end; 
    %else %if "%upcase(&iQuoteChar)" eq "SINGLE" %then %do; 
     %let quotechar = %nrstr(%'); 
    %end; 
    %else %do; 
     %let quotechar = %nrstr(%"); 
     %put WARNING: MACRO.DS2LIST.SAS: PARAMETER IQUOTECHAR INCORRECT. DEFAULTED TO DOUBLE; 
    %end; 
    %end; 
    %else %do; 
    %let quotechar = ; 
    %end; 

    /* 
    ** ENSURE ALL THE REQUIRED PARAMETERS WERE PASSED IN. 
    */ 
    %if "&iDs" ne "" and "&iField" ne "" %then %do; 

    %let dsid=%sysfunc(open(&iDs(&iDsOptions),i)); 
    %if &dsid %then %do; 

     %let pos=%sysfunc(varnum(&dsid,&iField)); 
     %if &pos %then %do; 

     %let rc=%sysfunc(fetch(&dsid)); 
     %do %while (&rc eq 0); 

      %if "%sysfunc(vartype(&dsid,&pos))" = "C" %then %do; 
      %let value = %qsysfunc(getvarc(&dsid,&pos)); 
      %if "%trim(&value)" ne "" %then %do; 
       %let value = %qsysfunc(cats(%nrstr(&value))); 
      %end; 
      %end; 
      %else %do; 
      %let value = %sysfunc(getvarn(&dsid,&pos)); 
      %end; 

      /* WHITESPACE/CARRIAGE RETURNS REMOVED IN THE BELOW LINE */ 
      /* TO ENSURE NO WHITESPACE IS RETURNED IN THE OUTPUT. */ 
      %if &cnt ne 0 %then %do;%unquote(&iDelimiter)%end;%unquote(&quotechar&value&quotechar.) 

      %let cnt = %eval(&cnt + 1); 
      %let rc = %sysfunc(fetch(&dsid)); 
     %end; 

     %if &rc ne -1 %then %do; 
      %put WARNING: MACRO.DS2LIST.SAS: %sysfunc(sysmsg()); 
     %end; 

     %end; 
     %else %do; 
     %put ERROR: MACRO.DS2LIST.SAS: FIELD &iField NOT FOUND IN DATASET %upcase(&iDs).; 
     %end; 
    %end; 
    %else %do; 
     %put ERROR: MACRO.DS2LIST.SAS: DATASET %upcase(&iDs) COULD NOT BE OPENED.; 
    %end; 

    %let rc=%sysfunc(close(&dsid)); 

    %end; 
    %else %do; 
    %put ERROR: MACRO.DS2LIST.SAS: YOU MUST SPECIFY BOTH THE IDS AND IFIELD PARAMETERS TO CALL THIS MACRO.; 
    %end; 

%mend;