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

[A_SAS_Man]

I am trying to pull data from our warehouse based on a list of ids stored in a SAS data set. Example of ids below:

data have ;                                                                                                                             
  input id;                                                                                                            
  cards ;                                                                                                                               
 1                                                                                                                       
 2                                                                                                                   
 3                                                                                                                      
 4                                                                                                                
 5                                                                                                                   
 6                                                                                                                  
 7                                                                                                                   
 8                                                                                                                  
 9                                                                                                                    
10                                                                                                                       
;                                                                                                                                       
run ;  

The problem is that the following statement fails because you can't reference a list of values stored locally as far as I know when pulling from a odbc connection so the following fails because of an invalid object name.

proc sql stimer;
	connect to odbc (DSN = Warehouse_Name);
	create table target_data as 
		select * from connection to odbc (
select a,b,c,d
from table.column
where a in (select distinct id from have)
);
	disconnect from odbc;

quit;

The actual data I'm doing this for is very large, hundreds of thousands of ids. So manually entering the into the where statement is not an option. I have attempted concatenating rows of the table "have" to try and automate putting them into the where statement but I run into character limits when trying to make it work for large amounts of data. I have also looked into creating temp tables on the server, but so far have not been able to successfully populate a temp table with data from a local table for a SQL server. I am not sure if this is because of a restriction specific to our environment or because of a syntax issue, I have been looking at the following other related pages for this:

https://communities.sas.com/t5/SAS-Programming/Creating-temp-table-in-sql-server-pass-through/td-p/4...

https://communities.sas.com/t5/SAS-Programming/create-temporary-table-in-SAS/td-p/343377

https://stackoverflow.com/questions/53808644/prepare-sas-data-to-populate-sql-server-temporary-table...

Is there another way to do what I'm trying to do other than the two options I have mentioned? Please let me know if clarification is needed.

[r_behata]

Untested code , Try Call Execute :

data have;
input id;
cards;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
;
run;

Data _null_;
	set have end=eof;

	if _n_=1 then
		do;
			call execute('proc sql stimer ;
				connect to odbc (DSN = Warehouse_Name);
				create table target_data as 
				select * from connection to odbc (
				select a,b,c,d
				from table.column
				where a in (');
		end;

	call execute(' ' !! catx(' ',id,',') !! ' ');

	if eof then
		do;
			call execute(');
				disconnect from odbc;

				quit;');
		end;
run;

[A_SAS_Man]

Thank you for the suggestion... As of right now I can't get this to work. I am getting some strange errors, parts of the code in the log are just underlined in red and don't have a specific error? I haven't seen that before. It may be a problem on my end translating your code for my example problem into my actual database query so I'll look at that some more.

[ChrisNZ]

@r_behata 's solution works on any number of values (provided the SQL query and the in() list can be of any length). 

Make it work with just 2 values and then add more.

[Kurt_Bremser]

Upload the dataset to the DB, and create a view that does the select; then download the result.

If your local selection dataset is sufficiently small, you can create the where statement dynamically:

proc sql noprint;
select quote(id) into :inlist separated by ',' from have;
connect to odbc (DSN = Warehouse_Name);
create table target_data as 
select * from connection to odbc (
  select a,b,c,d
  from table.column
  where a in (&inlist.)
);
disconnect from odbc;
quit;

[A_SAS_Man]

I am attempting to do this and I'm getting the following error: "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." Any idea what that could be linked to? I am unable to share the exact code/log as it contains our table names and structures.

[Kurt_Bremser]

Make sure that the macro variable looks like the DB expects a value list. You might have to use single quotes or a delimiter other than the comma.

I really have no clue about SQL Server.

[A_SAS_Man]

I feel like I'm very close on this, I was able to get your code to work by taking out the Quote() function as my data already had quotes. So this seems to work but I'm running into character limitations again when I try it for my full data set. Is there anyway around this to your knowledge?

ERROR: The length of the value of the macro variable INLIST (65540) exceeds the maximum length (65534). The value has been 
       truncated to 65534 characters.

[Krueger]

Your literally exceeding the max length by 6 values. 

It may have been mentioned already but why not just select the list into a separate table/dataset and then have you IN statement reference that table/dataset? 

Edit: I'm confused with your original post. I select Millions of records into datasets every day and cross reference these with PROC SQL. Are you actually selecting your list into a dataset?

[A_SAS_Man]

I'm exceeding the max by a lot more than that, it just stopped trying to write to the macro after it exceeded.

I'm not sure if I'm understanding your second part so correct me if I'm misunderstanding, but I mentioned in initial statement that since I'm pulling from our warehouse I can't just do a simple sub query, I have attempted to write data to a temp table on the database server but haven't been successful, possibly because of permissions issues.

[Krueger]

Not even a temp table but is there something preventing you from just pulling the list into a dataset? Lets assume the list is on your server, dbo.LIST.

Can you not just do:

proc sql;
connect to odbc (DSN = Warehouse_Name);
create table list as 
select * from connection to odbc (
  select id
  from dbo.LIST
);
disconnect from odbc;
quit;

Then repeat your prior code and INSTEAD of doing the variable just do IN (SELECT ID FROM LIST)?

If there's duplicates then just do a simple proc sort nodup on the LIST dataset at that point.

[A_SAS_Man]

I will have to test that more, I'm getting the same errors I receive when I try to build a temp table on the database server.

[Tom]

---

@A_SAS_Man wrote:

I feel like I'm very close on this, I was able to get your code to work by taking out the Quote() function as my data already had quotes. So this seems to work but I'm running into character limitations again when I try it for my full data set. Is there anyway around this to your knowledge?

ERROR: The length of the value of the macro variable INLIST (65540) exceeds the maximum length (65534). The value has been 
       truncated to 65534 characters.

---

That is why someone already posted how to build the code using CALL EXECUTE().  If that is too hard to understand/debug then just write the generated code a text files instead and use %INCLUDE to run it.

Something like:

filename code temp;
data _null_;
  file temp;
  set list end=eof;
  if _n_=1 then put
/'create table want as '
/'select * from connection to odbc'
/'(select * from mytable'
/' where id in'
/'(' @
  ;
  else put ',' @;
  put id ;
  if eof then put 
'));'
  ;
run;

proc sql ;
connect to odbc .... ;
%include code ;
quit;

Note you should make sure to check that SQL Server doesn't garf at getting such a long list of values in a single command.  You could try writing the code to a permanent file instead of the TEMP file in my example. Then you could try running the generated SQL Server code with some other tool and see how it works.

[Kurt_Bremser]

Then your data is not "sufficiently small". Upload your dataset to the SQL Server and run the select there.

[A_SAS_Man]

 Is this different from creating temp tables? What exactly do you mean by upload the dataset?