Need to extract SQL code from a .txt file and store it in a string variable

[kazoli]

Hello - I'm brand new to SAS and am struggling with something. I have SQL code stored in a .txt file. I would like to read the .txt file contents into a single string variable in SAS that can then be called in a PROC SQL statement in order to run a query. The .txt file does have a few parameters/variables inside it that I will need to replace once imported into SAS, but I'm able to do that successfully. Below is a sample of the code I'm currently using. This process is able to get the .txt file contents, but it concatenates the lines in a way that makes the query error out due to formatting issues. Any point in the right direction is greatly appreciated.

FILENAME msghtml "//mypath/myfile.txt"
data _null_;
length text $32767;
retain text '';
infile msghtml flowover dlmstr='//' end=last;
input;
text = catx(text,_infile);
run;

[Tom · Accepted solution]

You are creating the value in a dataset variable, but since you are using _NULL_ dataset it is not being written anywhere.  If you want to store it in a macro variable then you need to add a CALL SYMPUTX() function call. Use the LAST variable you created with the INFILE statement to know when to create the macro variable.

The DMLSTR= option is doing nothing in your data step.  Are you trying to remove end-of-line comments that are marked with // ?  If so then try reading the line into a variable instead of using the _INFILE_ automatic variable. 

data _null_;
  length sql line $32767;
  retain sql ;
  infile msghtml flowover dlmstr='//' end=last;
  input line ;
  sql = catx(' ',sql,line);
  if last then call symputx('sql',sql);
run;

[kazoli]

Perfect, that did the trick - thank you very much! I found a lot of this code elsewhere and tried to apply it to my case, but this has taught me more in this area. Thanks again!