SAS ODBC with table in local library

Question

What if I run a create table using ODBC in SAS. This table is now saved to my permanent library in SAS. Now I want to take that table because it searched through millions of rows of data and after I am done I have filtered the items and this table has 664 distinct sys_id rows.

I need to take this 664 distinct sys_id rows and now I need to pull all sys_id's in the ODBC that match. I am looking to match any sys_id that has a medical claim during a certain period of time. I know how to do the query part but not sure how to connect a table in my local library and an ODBC at the same time. I have tried doing tons of different things like libname test.sys_id left out join to galaxy.sys_id but nothing works. I have also tried to read up on if this is even possible. I am thinking it might not be possible. The odd thing is I can do it in Access by taking the table I create and connecting it to a table on the server so I would think it would be possible with SAS. I cannot run this program in Access. Not enough memory. Any advice?

Below is the code I have tried so far:

 /***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

Since you are able to do everything that you want EXCEPT join a table from your local machine with the ODBC data, it would seem that a subquery would work.

Once your subquery gets the 664 sys_ids, that small subset is joined with the ODBC data to return only the desired records...which should not be an unreasoable amount of records

Proc SQL Subquery Links Here and Here

If you are familiar with the HASH object in SAS...this is similar. Or previously, using Proc SQL to create a macro variable with all the sys_ids separated by commas and used with an IN operator in a Data step (like what @Rob Penridge uses in his macro).

Answer 2

Excellent question... We have a macro that we use here to get around that issue as we don't have to ability to upload files to the ODBC server or create temp tables etc... A simple example of using the macro is:

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;

Although the example above doesn't use ODBC passthrough it will work fine with it. And if OPTION MPRINT is on then the log would show something like the below:

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

As you can see it produced a comma separated list of names, and quoted the names. You can change the delimiter as well as the quotes that are used. There is no limit to the number of items in the list (we've used it on lists with over 100k items) as the list is 'streamed' by the macro, not stored in a macro variable. The only size limit is the one enforced by the ODBC server's querysize. The code to the macro is a little bit scary but place it in your macro autocall folder and forget about it.

The macro code is below:

/***************************************************************************
**  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;