Pulling Data from SQL Server Based on List of Values in SAS Table

[Tom]

So you are talking about a macro that was only provided as an attachment to one of the responses.  And even then in a file format that the forum cannot display without having to download the file.

Yes that macro appears to be stripping periods and other characters from a number of the input parameters.  I suspect they did that to force them to be a single level name. Hard to tell why they thought that would help as it just causes the type of confusion you got.

[Patrick]

@A_SAS_Man 

Reading through some of this long discussion I believe no one proposed to use data set option DBMASTER yet - example here.

If I understand this right then logically you need nothing else than the result of an inner join over the ID column. The "only" challenge is that the two tables reside in different places.

The SQL you've posted shows nothing database specific which SAS couldn't push if using SAS SQL. You could give code like below a try.

libname sqlsrc odbc .....;

options sastrace='d' sastraceloc=saslog nostsuffix;
proc sql stimer;
  create table target_data as 
    select sqlsrc.*
      from sqlsrc.dbtable(dbmaster=yes), sassrc.idtable
        where sqlsrc.id=sassrc.idtable
  ;
quit;

I believe SAS will translate above syntax into an IN clause with the values of the ID's from the SAS table; and it will issue multiple SQL's if not all values can fit into a single IN clause.

NB: Make sure the ID's in the source SAS table are unique before using the table in the SQL. 

[A_SAS_Man]

This seems like a very elegant solution if i can get it to work, but right now it seems to be running into the same issues I was having previously. Below is the structure of my code.

options sastrace='d' sastraceloc=saslog nostsuffix;
proc sql stimer;
  create table target_data as 
    select sqlsrc1.a,
           sqlsrc2.b,
           sqlsrc3.c
    from sqlsrc1 inner join sqlsrc2 on (sqlsrc1.a=sqlsrc2.a)
         left join sqlsrc3 on (sqlsrc1.a=sqlsrc3.a)
         (dbmaster=yes), sassrc.idtable
    where sqlsrc.id=sassrc.idtable
;
quit;

One question, in sassrc.idtable, "sassrc" is a table, correct? And "idtable" would be the column corresponding to the id? I'm running into issues with that structure. The specific error I'm getting is "Incorrect syntax near 'dbmaster'". Making me think that I need to put the dbmaster statement elsewhere when there are joins within the warehouse happening. I'll continue to troubleshoot and post if I get it working but I'm open to suggestions once again if you have encountered something like that.

[Patrick]

@A_SAS_Man 

DBMASTER must be next to the big table in the database. I also don't see any librefs in your syntax but I guess not all your tables are in SAS WORK.

Which tables are in the database, in SAS?

If using DBMASTER try and keep the SQL syntax as simple as possible. At least for your first function test may-be don't use the left join.

[A_SAS_Man]

So was my assumption correct that "sassrc" is a table? Or are you saying that is a library where my table resides?

[Patrick]

---

@A_SAS_Man wrote:

So was my assumption correct that "sassrc" is a table? Or are you saying that is a library where my table resides?

---

The syntax is: <libref>.<table name> so sassrc and sqlsrc are librefs in the sample code I've posted.

[A_SAS_Man]

So I assume there needs to be just the one column in that table then so it knows what to reference? Is this the same for the SQL source? So the where statement should look like the following?

where sqllibrary.table=work.table

In this example, how does it know which element to match on?

Even when I make that correction and simplify my SQL query I am getting similar errors (referring to "near "=" which is the equal sign in dbmaster=yes). I don't have a libref for my SQL source, I am connecting with connect to odbc statement.

options sastrace='d' sastraceloc=saslog nostsuffix;
proc sql stimer;
  connect to odbc (DSN = dsn);
  create table target_data as
  select * from connection to odbc (
    select sqlsrc1.a
    from sqlsrc1 (dbmaster=yes), sassrc.idtable
    where sqlsrc1.id=work.idtable
);
disconnect from odbc;
quit;

ERROR: CLI describe error: [Easysoft][SQL Server Driver][SQL Server]Incorrect syntax near '='. : [Easysoft][SQL Server Driver][SQL 
Server]The batch could not be analyzed because of compile errors.

[Tom]

You seemed to have missed a key point.  You need to write SAS code. Not SQL Server code.  So do not use the FROM CONNECTION TO clause. You need to make a LIBNAME statement instead of a CONNECT statement.

You also seem confused about how to use SQL syntax (in SAS or on SQL server) to reference datasets and variables.  Typically it helps make the queries clearer it you define your own alias for each source dataset (what SQL might call a table) and use the alias when referencing a variable (what SQL might call a field or a column).

So to join the dataset TABLENAME from your SQL Server database first you would make a libref that points to the location in your SQL server where TABLENAME lives so that you can refer to it without any periods in the name.

For example here is code to join TABLENAME and the SAS dataset named WORK.IDLIST use the variable ID.

options sastrace='d' sastraceloc=saslog nostsuffix;
libname SQLSRV odbc dsn=dsn  ;
proc sql stimer; 
create table WORK.target_data as
  select s1.a
  from SQLSRV.tablename (DBMASTER=YES) as S1
  inner join WORK.IDTABLE as S2 
    on S1.id = S2.id 
;
quit;

So in the SQL code I defined and used the aliases S1 and S2 for the two source datasets, one from SQL server and one from your SAS session.  These were used in generating the list of variables to select and when making the conditions to test to do the joining.

Also notice that I defined a libref of SQLSRV.  SAS librefs need to be valid SAS names of at most 8 characters. In a sense they serve a similar role to the alias in the SQL code.

Also think about whether you can write the results into a dataset (table) in your remote database instead of pulling down to SAS be writing to WORK, or some other SAS libref.   That is something you can play around with once you see whether SAS is actually able to detect that it should generate the SQL to run in the SQL server database by just listing the values that it pulls from the IDTABLE dataset.

[Patrick]

@A_SAS_Man Looks like we need to get back to basics. 

So, step by step:

1. You need a SAS libname which points to the database. Do you have such a libname which works?

2. You need to formulate the SQL in the SAS SQL flavour so forget about the CONNECT statements. That's for explicit pass-through SQL formulated in the database SQL flavour

"Fill out" below code "template" and see if this works for you.

libname sqlsrc odbc <here the connection info to your database similar to what works in the connect statement>;

options sastrace='d' sastraceloc=saslog nostsuffix;
proc sql stimer;
  create table target_data as  
    select 
      a.<db column name1>,
      a.<db column name2>,
      a.<...only list the names you want in your target table> 
    from 
      sqlsrc.<db table name>(dbmaster=yes) a, 
      <sas libref, can be WORK>.idtable b
    where 
      a.<column name in database table>= b.<column name in SAS table>
    ;
quit;

[ChrisNZ]

If you want to use my macro, the syntax is pretty simple.

The parameter varin is a variable name.

What is the issue? By the way, this macro is to be used inside an explicit pass-through query if needed.

What is missing?

[A_SAS_Man]

As I posted above in the thread when I use your macro it was removing "." that were in my variable name. I believe I found a workaround by modifying your macro as I noted above. Still testing to make sure it's working.

[ChrisNZ · Accepted solution]

Mmm I never thought a variable name could contain dots, but I guess this makes sense in some cases.

I've updated the macro but I can't test now, please see if this solves the issue.

[ScottBass]

I haven't read the 6 pages of posts, but perhaps this gets you going?  I've provided a few approaches.

(If your post is solved please mark an answer out of the ***6 pages*** of posts!).

* set desired SAS options ;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options dbidirectexec;
options msglevel=I;
options fullstimer;
options mprint mrecall;
options compress=binary;
options ls=max;
options nocenter;
options lognumberformat=1;

* allocate libref to SQL Server ;
libname SQLSVR odbc 
   NOPROMPT="Driver={SQL Server Native Client 10.0};Server=SVDCMHDVSDWSQD1;Database=RLDXHosp;Trusted_Connection=yes;" 
   schema=dbo 
   bulkload=yes 
   dbcommit=100000 
   direct_exe=delete 
   preserve_names=yes
;

* create some test data ;
proc datasets lib=sqlsvr nowarn nolist;
   delete zipcode;
run;

data zipcode / view=zipcode;
   * surrogate key ;
   sk+1;
   set sashelp.zipcode;
run;

proc append base=sqlsvr.zipcode data=work.zipcode;
run;

* create a sample subset file, say 1/2 of the source table ;
data subset / view=subset;
   set sqlsvr.zipcode (keep=sk);
   if mod(sk,2)=0;
run;

*** UPLOAD THE SUBSET FILE TO SQL SERVER *** ;
proc datasets lib=sqlsvr nowarn nolist;
   delete subset;
run;

proc append base=sqlsvr.subset data=work.subset;
run;

* Use explicit passthrough to subset the data ;
* option 1: inner join ;
proc sql;
   connect using sqlsvr;
   create table want1 as
   select *
   from connection to sqlsvr (
SELECT a.*
FROM   dbo.zipcode a
JOIN   dbo.subset  b
ON     a.sk=b.sk
   );
quit;

* option 2: nested query ;
proc sql;
   connect using sqlsvr;
   /* create on SQL Server */
   execute by    sqlsvr (
DROP TABLE IF EXISTS dbo.want2
SELECT a.*
INTO   dbo.want2
FROM   dbo.zipcode a
WHERE  a.sk IN (SELECT sk FROM dbo.subset)
ORDER BY sk
   );
   /* Copy back to SAS via explicit passthrough */
   create table want2 as
   select *
   from connection to sqlsvr (
SELECT * 
FROM   dbo.want2
ORDER BY sk
   );
   /* Copy back to SAS via ODBC engine */
   create table want3 as
   select *
   from sqlsvr.want2
   order by sk
   ;
quit;

* cleanup ;
proc sql;
   connect using sqlsvr;
   execute by    sqlsvr (
/* SQL Server 2016 and above */
DROP TABLE IF EXISTS dbo.zipcode
DROP TABLE IF EXISTS dbo.subset
DROP TABLE IF EXISTS dbo.want2
   );
quit;   

[A_SAS_Man]

When I run through the following step.

proc append base=sqlsvr.zipcode data=work.zipcode;
run;

I am getting a warning that the base data set does not exist followed by an error "Invalid object name 'zipcode'.". Possible I'm missing something but it does seem like in your code no table exists on the server side named "zipcode" prior to you trying to append to it.

[Tom]

Try adding the FORCE option to the PROC APPEND statement. Then it should create the table.
If you have trouble with SAS creating the table you might create the table first. But if you are using a temporary table that might be an issue.