Run SQL statement from file to create data in SAS

Question

I have very little experience in SAS. I do have experience in SQL.

I want to do the following: - Use a SQL statement that is stored in a text file to import data into SAS.

What works is to copy and paste the SQL server query and run it as a pass-through query in SAS. I get the data (after a few minutes).

But I would like to be able to manage and develop the SQL script in SSMS, and store the script in a sql file. So I tried the following:

proc sql;
connect to ODBC("dsn=DatabaseOfInterest");
create table NewDataSet as select * from connection to odbc(
%include 'C:\sqlscript.sql';
);
quit ; 

This does not work and creates the following error:

**ERROR: CLI prepare error: 
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '%'. 
**

Is there a way to achieve this?

Answer 1

I don't know if there's a truly clean way to work around this. The issue is that the connect to SQL is passing %include to the SQL parser, which is of course incorrect compared to what you intend.

It will, however, correctly resolve macros and macro variables, so you can read your SQL command into a macro variable and use it that way. One way to do that is below.

filename tempfile temp;  *imaginary file - this would be your SQL script;
data _null_;             *creating a mocked up SQL script file;
file tempfile;
put "select * from country";
run;

data _null_;             *reading the SQL script into a variable, hopefully under 32767?;
infile tempfile recfm=f lrecl=32767 pad;
input @1 sqlcode $32767.;
call symputx('sqlcode',sqlcode);  *putting it into a macro variable;
run;


proc sql;                *using it;
connect to oledb(init_string=&dev_string);
select * from connection to oledb(
&sqlcode.
);
quit;