topic Re: Need to extract SQL code from a .txt file and store it in a string variable in SAS Programming

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

kazoli — Fri, 26 Nov 2021 16:29:06 GMT

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;

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

Tom — Fri, 26 Nov 2021 16:40:35 GMT

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;

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

kazoli — Fri, 26 Nov 2021 19:05:19 GMT

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!