CODEWITHSUNDEEP
fileimportsas
ADJ
ADJ

Reputation: 5282

Use code in a file as content of a SAS macro variable

I have a series of files, each containing SQL code. I'd like to use SAS to run passthrough queries using that SQL. Since those files are changing often I just want an automated way to keep SAS synched up with these files. I'm thinking that if I could import the SQL file and put it into a macro variable, then I could just use the macro within my SAS passthrough and everything would be in synch. But I wouldn't know how to read the external SQL in anything other than a SAS dataset, which wouldn't make a lot of sense to start with... Example: sqlcode.sql

select *
from table1

In SAS:

/*Somehow read sqlcode.sql into a macro variable sassql*/

proc sql;
connect to netezza (SERVER=MYSERVER DATABASE=MYDBS);

execute (
&sassql.

) by netezza;

;
quit;

Upvotes: 0

Views: 738

Answers (3)

Robert Penridge
Robert Penridge

Reputation: 8513

I've run into this issue a few times so I wrote my own macro version of %include called %include_file(). Looking over my notes it should work although if the file you are including contains macro tokens you may need to adjust the macro quoting function.

It will work on a file of any size (as it reads it one line at a time and doesn't try and store the whole file to a variable).

Simply call it like this:

proc sql;
connect to netezza (SERVER=MYSERVER DATABASE=MYDBS);
execute (
  %include_file(iFileName=c:\mypath\mysasfile.sas);
) by netezza;
;
quit;

The code is below. I suggest putting it in your macro autocall library:

%macro include_file(iFileName=);
  %local filrf rc fid rc2;

  %let filrf=myfile2;

  %let rc=%sysfunc(filename(filrf, &iFileName, , lrecl=32767));
  %let fid=%sysfunc(fopen(&filrf,i,32767,b));

  %if &fid > 0 %then %do;
    %let rc = %sysfunc(fread(&fid));
    %do %while(&rc eq 0);  
      /* NOTE THE STACKED COMMANDS BECAUSE WE ARE RETURNING THE CONTENTS OF MACRO VAR C HERE AND DONT WANT WHITESPACE */
      %let rc2=%sysfunc(fget(&fid,c,32767));%quote(&c)%let rc = %sysfunc(fread(&fid)); 
    %end;
  %end;
  %else %do;
    %put C &fid %sysfunc(sysmsg());
  %end;
  %let rc=%sysfunc(fclose(&fid));
  %let rc=%sysfunc(filename(filrf));
%mend;

Upvotes: 0

Jeff
Jeff

Reputation: 1807

Read in your files with datasteps, then follow those up with sql select-into queries like this:

data query_text;
    infile 'sql query.sql';
    input line $1000;
run;

proc sql;
    select * from query_text into :sassql separated by ' ';
quit;

The whole read-in file will be dumped to a macro variable.

Upvotes: 0

DWal
DWal

Reputation: 2762

EDIT-new answer that actually works:

You could read your entire query into a macro variable through a data step, but you'd be limited to 32,767 total characters in the query, as that is the most a character variable would hold.

I'd suggest using a data step to read your query line by line, putting the entire query into a temp file, and including the temp file using %include:

filename query temp;
data _null_;
  infile 'C:\My Documents\sql query.sql' end=eof truncover;
  input @1 line $32767.;
  file query;
  if _n_=1 then put  'proc sql;
                      connect to netezza (SERVER=MYSERVER DATABASE=MYDBS);
                      execute (';
  put                 line;
  if eof then put    ') by netezza;
                      quit;';
run;
%include query / source2;
filename query clear;

Here, the temp file is "query", thedata step reads the sql file line by line and outputs each line to the temp file.

Upvotes: 2

Related Questions