Proc SQL - lengthy dynamic sql statement in variable

[broth38]

I have a SAS program that loops through a CSV file and dynamically creates a SQL statement (well over 1000 characters). I have a macro variable that contains the SQL statement and I can %PUT the entire statement to the log, but I have not found a method to run this statement in a Proc SQL. The variable truncates. I have tried to piece together %substr of the macro variable, but the execution always evaluates them individually. Is there a method/workaround to run my lengthy dynamic SQL statement?

Thanks!

[PaigeMiller]

As always, show us.

WE need to see the SASLOG (run with OPTIONS MPRINT; turned on).

We need to see the results of the %PUT statement.

And we need to see anything else that might be relevant here (you figure out what is relevant, we don't know because we are not in your situation).

[Urban_Science]

One idea would be to use CALL EXECUTE in a data step and that way you do not need to even worry about the macro world.

[broth38]

Thanks for the quick response! Much appreciated.

data SQL_Lines;
infile 'SQL_Dimensions_Test1.csv' dlm=',' dsd truncover firstobs=2 end=last;
input ReportNo $ VarName $ ProvName $ RowLabel $ Map1 $ Map1_Oper1 $ Map1_Oper2 $ Map1_Value1 $ Map1_Value2 $ Map2 $ Map2_Oper1 $ Map2_Oper2 $Map2_Value1 $ Map2_Value2 $ Map1_NS $ Map2_NS $;

length strSQL $10000;

.

.

.

strSQL = strip(strSQL) || " AND "...

.

.

.

retain strSQL;

.

.

.
run;

Once the strSQL SQL statement has been assembed, what would be the Call Execute syntax to successfully run the SQL statement?

Thanks!

[Urban_Science · Accepted solution]

Basically, the string in CALL EXECUTE should be a valid SAS statement that you could copy, paste, and run as is.  In your case it should have "PROC SQL;" in the front and "QUIT;" at the end of the string.  I think the only thing I would change in your code is to add a little something at the end.

.
.
.
retain strSQL;
.
.
.
if last then do;
   strSQL = cat("PROC SQL; ", StrSQL, "QUIT;");
   CALL EXECUTE(strSQL);
end;
run;

This way it only executes it when it reaches the end of the csv.  You could also throw "output;" in there so you can see the table with only the last row (to inspect the generated SQL string). 

[broth38]

Thank you very much. That worked great! Very much appreciated.

[PaigeMiller]

Wait ... you are using SQL language in the macro variable, but you want to use it inside of a data step?

This will always fail. You can only use data step language inside a data step.

But I did ask to see the SASLOG run with OPTIONS MPRINT turned on, this has not been provided. I did ask to see the entire results of the %PUT command, this has not been provided.

[Urban_Science]

I believe that @broth38 has an existing process that was creating SQL statements from a csv, but couldn't figure out how to run them once created.  Judging by the input statement in the reply, the csv might list out different variable transformations/equations to be dynamically created. 

For example:

data have;
   length statement $ 1000;
   statement = "create table want as";
   output;

   statement = "select";
   output;

   statement = "*";
   output;

   statement = "from have2;";
   output;
run;

data want;
   set have end=last;
   retain sqlStr;
   length SqlStr $10000;
   
   sqlStr = catx(" ", sqlString, statement);
   
   if last then do;
      sqlStr = catx(" ", "PROC SQL;", StrSQL, "QUIT;");
      output;
   end;
run;

data _NULL_;
   set want;
   call execute(sqlStr);
run;

The have dataset is where the SQL strings are being created (I can't ever get datalines to work right the first time which is why there is a bunch of output statements in there).  The want dataset is where SAS puts together the SQL string to be ran.  The data _NULL_ step is when that SQL code is ran (I broke it out into a separate step from my previous reply).