topic Re: How can I read in .SQL files and have them run within SAS? in SAS Programming

How can I read in .SQL files and have them run within SAS?

InspectahDex — Tue, 02 Feb 2021 23:32:38 GMT

A colleague of mine does not uses SAS but uses R and SQL Server Management Studio. I need to collaborate with him to write SQL code but we were wondering if there was a way to import .SQL code (queries) into a SAS program and have them run within SAS. I couldn't find anything online but it seems like the only option is to copy and paste each SQL file separately into a SAS Program. Is there a better way to do this so SAS can run and write to a .SQL file?

Re: How can I read in .SQL files and have them run within SAS?

ChrisNZ — Wed, 03 Feb 2021 00:55:07 GMT

You can import sql files:

data _null_; 
  file "&wdir\t.sql";
  put 'select * from sashelp.class;';
run;
options source2;
proc sql;
  %include "&wdir\t.sql";
quit;

Running the file as pass-through SQL would be trickier.

Re: How can I read in .SQL files and have them run within SAS?

ChrisNZ — Wed, 03 Feb 2021 00:58:58 GMT

Maybe something like:

data _null_; 
  file "&wdir\t-sql.sql";
  put ' * from [dbo].[tab1]' ;
run;
options source2;
proc sql;  
  connect using SQLSVR;
  select * from connection to SQLSVR(
    select
    %include "&wdir\t-sql.sql" ;
    order by AGE
  );
quit;

Re: How can I read in .SQL files and have them run within SAS?

NarutoTaka — Tue, 29 Nov 2022 09:05:02 GMT

Hi,

I have a task where I need to read multiple texts files containing SQLs from a shared location on SAS studio and dump/export the output to a folder in SAS only.

I am able to pick, read and run one .text file containing a SQL and export that to a folder. But how do I do if I have like 10 .text files present in the input location. Please help on this.

data _null_; *reading the SQL script into a variable, hopefully under 32767?;
infile "/dslanalytics-shared/dgupt12/SQLs/Query.txt" recfm=f lrecl=32767 pad;
input @1 sqlcode $32767.;
call symputx('sqlcode',sqlcode); *putting it into a macro variable;
run;

proc sql;
connect to odbc as mycon (complete="DRIVER={SnowflakeDSIIDriver};
SERVER=;
UID=&usr.;
PWD=&pwd.;
WAREHOUSE=;
DATABASE=;
SCHEMA=;
dbcommit=10000 autocommit=no
readbuff=200 insertbuff=200;");

create table final_export as
select * from connection to mycon(&sqlcode.);
disconnect from mycon;
quit;

proc export data = work.final_export
outfile = "/dslanalytics-shared/dgupt12/Report/final_report.csv"
DBMS = csv REPLACE;
run;

Re: How can I read in .SQL files and have them run within SAS?

SASKiwi — Tue, 29 Nov 2022 19:18:01 GMT

The way you are doing it is complicated and also macro variables are limited to a length of 32K. Using @ChrisNZ 's %INCLUDE approach is way better:

%macro Run_SQL (SQL_File = , CSV_File = );

proc sql;
connect to odbc as mycon (complete="DRIVER={SnowflakeDSIIDriver};
SERVER=;
UID=&usr.;
PWD=&pwd.;
WAREHOUSE=;
DATABASE=;
SCHEMA=;
dbcommit=10000 autocommit=no
readbuff=200 insertbuff=200;");

create table final_export as
select * from connection to mycon(
%include "/dslanalytics-shared/dgupt12/SQLs/&SQL_File.";
);
disconnect from mycon;
quit;

proc export data = work.final_export
outfile = "/dslanalytics-shared/dgupt12/Report/&CSV_File."
DBMS = csv REPLACE;
run;

%mend Run_SQL;

%Run_SQL (SQL_File = Query1.txt, CSV_File = final_report1.csv);
%Run_SQL (SQL_File = Query2.txt, CSV_File = final_report2.csv);

Re: How can I read in .SQL files and have them run within SAS?

NarutoTaka — Thu, 01 Dec 2022 17:19:25 GMT

@SASKiwi Thank you very much for the approach. This seems very easy from the one I am using.

But I am getting the below error while running the below code for the %Include statement. (File attached). I have executed the code as it is as you had shared. I have just updated the connection details to run. Was anything else needed to be changed?

And the output part which you have mentioned will have to written for each file, how do I get it to work dynamically which takes the name of the text file from the input location and generate output file with the same name?

Any help on this will be very much appreciated. I am new to SAS stuck with this task now.

Re: How can I read in .SQL files and have them run within SAS?

Tom — Thu, 01 Dec 2022 18:18:00 GMT

Do not attach files to share error messages. Just copy the text and paste it into the pop-up window you get when you click on the Insert Code icon.

If you must post a photo then just paste it in or use the Insert Photos icon.

I cannot see the files because the site the SAS communities uses is blocked by my company. Here is what your DOCX file looks like when I attempt to view it using the link the forum creates.

Re: How can I read in .SQL files and have them run within SAS?

NarutoTaka — Fri, 02 Dec 2022 03:29:13 GMT

@Tom Sorry, about that. I am getting this error when the code is taking the input files for processing. I hope it helps now.

/*1st error*/ 

MPRINT(RUN_SQL):   create table final_export as select * from connection to mycon( %include 
 "/dslanalytics-shared/dverma1/sql_to_exl/input_sql/Query1.txt";
 MPRINT(RUN_SQL):   );
 ERROR: CLI prepare error: SQL compilation error: syntax error line 1 at position 0 unexpected '%'.
 SQL statement: %include "/dslanalytics-shared/dverma1/sql_to_exl/input_sql/Query1.txt";.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 MPRINT(RUN_SQL):   disconnect from mycon;
 NOTE: Statement not executed due to NOEXEC option.
 MPRINT(RUN_SQL):   quit;
 NOTE: The SAS System stopped processing this step because of errors.

/*2nd Error*/

SYMBOLGEN:  Macro variable SQL_FILE resolves to Query2.txt
 MPRINT(RUN_SQL):   create table final_export as select * from connection to mycon( %include 
 "/dslanalytics-shared/dverma1/sql_to_exl/input_sql/Query2.txt";
 MPRINT(RUN_SQL):   );
 ERROR: CLI prepare error: SQL compilation error: syntax error line 1 at position 0 unexpected '%'.
 SQL statement: %include "/dslanalytics-shared/dverma1/sql_to_exl/input_sql/Query2.txt";.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 MPRINT(RUN_SQL):   disconnect from mycon;
 NOTE: Statement not executed due to NOEXEC option.
 MPRINT(RUN_SQL):   quit;
 NOTE: The SAS System stopped processing this step because of errors

Re: How can I read in .SQL files and have them run within SAS?

Tom — Fri, 02 Dec 2022 03:47:41 GMT

It does not really work to try to invoke %INCLUDE in the MIDDLE of a statement.

Try copying the original file into another file with the extra code before and after it.

I don't have access to T-SQL but lets just do an example with a normal SAS SQL query instead.

So first create a file with the query. Let's call that file SQLCODE .

Then copy that into a file name WANT that adds some text before and after it.

Then use %INCLUDE to run it.

filename sqlcode temp;
data _null_; 
  file sqlcode ;
  put ' select * from sashelp.class ' ;
run;

filename want temp;

data _null_;
  infile sqlcode end=eof;
  file want;
  if _n_=1 then put 'create table class as (' ;
  input;
  put _infile_;
  if eof then put ');' ;
run;

proc sql;
%include want / source2;
quit;

Results:

2346  filename sqlcode temp;
2347  data _null_;
2348    file sqlcode ;
2349    put ' select * from sashelp.class ' ;
2350  run;

NOTE: The file SQLCODE is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: 1 record was written to the file (system-specific pathname).
      The minimum record length was 29.
      The maximum record length was 29.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


2351
2352  filename want temp;
2353
2354  data _null_;
2355    infile sqlcode end=eof;
2356    file want;
2357    if _n_=1 then put 'create table class as (' ;
2358    input;
2359    put _infile_;
2360    if eof then put ');' ;
2361  run;

NOTE: The infile SQLCODE is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: The file WANT is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: 1 record was read from the infile (system-specific pathname).
      The minimum record length was 29.
      The maximum record length was 29.
NOTE: 3 records were written to the file (system-specific pathname).
      The minimum record length was 2.
      The maximum record length was 29.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


2362
2363  proc sql;
2364  %include want / source2;
NOTE: %INCLUDE (level 1) file WANT is (system-specific pathname).
2365 +create table class as (
2366 + select * from sashelp.class
2367 +);
NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

NOTE: %INCLUDE (level 1) ending.
2368  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds