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

[Kurt_Bremser]

---

@A_SAS_Man wrote:

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

---

No, I mean to create a temporary table.

[jdwaterman91]

To piggyback off of the proposed solution @Kurt_Bremser has posted above, are there instances where your ID variable is missing? If so, that could throw off your macro variable string. Also, in the event you have to use single quotes:

Proc SQL Noprint;
Select Distinct "'" || ID || "'" Into :Inlist Separated By ',' 
From Have
Where ID ^= '';
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;

[Reeza]

You may find the solutions here from Tom do what you want.

https://stackoverflow.com/questions/52772490/create-several-sas-macro-variable-lists-from-single-dat...

1. Create multiple macro variables instead

2. Generate the code dynamically via PUT/CALL EXECUTE (Tom prefers PUT/INCLUDE, I prefer CALL EXECUTE)

@r_behata solution is what I would recommend here.

[A_SAS_Man]

Yeah, I am getting "Insufficient authorization to access temp.dat" errors from multiple of the solutions posted here (including @r_behata), so they must be attempting to make a table of some form on the database server.

I will have to check the suggestion about looping through multiple macro variables, that may be my best option. Thanks!

[Reeza]

No, that's from something else. @r_behata solution should work just fine - post the log if you'd like help debugging it. Even he said it was untested to it's likely something small - like a missing quote or semicolon.

[A_SAS_Man]

 So I'm not able to post the log directly because of it containing our data structures and stuff but I do feel as though I'm getting close with this one. One point of clarification, is the code supplied by @r_behata supposed to just generate a query with a filled out where statement that I can copy and paste from the log into a new query? Or is it actually attempting to run the query it generates with Call Execute? I'm not familiar enough with that function to understand exactly what it is trying to do with the quoted text inside of it. Currently, I am getting no errors, but it just goes to the log with the complete SQL statement (including all the values in the where statement) and does not pull any data. If that is what it is supposed to do then I'm not sure, there are lots of extraneous characters in it that can't just be copy/pasted from the log into a new query.

[Reeza]

It runs the query directly, that's what CALL EXECUTE does.
Read the last section on this tutorial to see how it works and calls the macro as needed.
Note that I use the STR variable to create the initial code and then use CALL EXECUTE at the end. I recommend switching to this method to make it easy to see the code generated and being run.

[A_SAS_Man]

Did you mean to link something in your last message?

[Reeza]

data have;
input id $;
cards;
Alfred
Jane
;
run;

Data _null_;
	set have end=eof;

	if _n_=1 then
		do;
			call execute('proc sql stimer ;
				select * from sashelp.class
				where name in (');
		end;

	call execute(quote(id));
	
	if not eof then call execute(", ");

	if eof then
		do;
			call execute(');');
		end;
run;

Here's a modification on @r_behata solution that you can run. It's designed to pull records requested from SASHELP.CLASS instead. 

And here's the link to a full tutorial on macros that uses call execute at the end. You'll want to run Step5/6 commenting out the call execute line unless you run it all. 

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

---

@A_SAS_Man wrote:

Did you mean to link something in your last message?

---

[Reeza]

Another version, with comments and you can view the demo data set to see the output explicitly. 

*create fake data to simulate ID list;
data have;
input id $;
cards;
Alfred
Jane
;
run;

Data demo;
	set have end=eof;

    *this runs once at the beginning only;
	if _n_=1 then
		do;
		    str1 = "proc sql stimer ;
				select * from sashelp.class
				where name in (";
			call execute(str1);
		end;

     *this changes for each line;
    str2 = quote(trim(id));
	call execute(str2);
	
	*this runs once for every line EXCEPT the last line, adds a comma for the query to be valid;
	if not eof then do;
		str3 = ", ";
	    call execute(str3);
	 end;

    *this runs once, only at the end to close query;
	if eof then
		do;
		   str4 = ");";
			call execute(str4);
		end;
run;

[A_SAS_Man]

I really appreciate all your help,unfortunately when I convert my query into each of two alternatives you have I am getting the exact same results as I did with @r_behata's solution. It just prints "NOTE: CALL EXECUTE generated line." and then the generated SQL query to the log without actually executing it. No warnings, no errors.

[Reeza]

Run the exact code I posted with the SASHELP. Does that work?

[Reeza]

Does it generate the data set though? It doesn't generate anything beyond that but should create the data set needed, are you checking for that?

[A_SAS_Man]

The code you posted that pulls from the SAS.help file works yes. It generates the dataset into the results tab (which, at some point I would actually need to get it into a table but I'm not concerned about that at this point). However when I convert that to actually pulling from our warehouse I get the above mentioned output to the log file.

[Reeza]

Did the query you post initially work for any records? We've been working on the assumption it did. 

But it's likely something as simple as having extra spaces in the values that are not trimmed. If you follow the approach in my last post, where the strings are created and you can view them you'll be able to see where it's going wrong and fix it. 

---

@A_SAS_Man wrote:

The code you posted that pulls from the SAS.help file works yes. It generates the dataset into the results tab (which, at some point I would actually need to get it into a table but I'm not concerned about that at this point). However when I convert that to actually pulling from our warehouse I get the above mentioned output to the log file.

---