topic Re: Proc SQL - lengthy dynamic sql statement in variable in SAS Programming

Proc SQL - lengthy dynamic sql statement in variable

broth38 — Wed, 01 Aug 2018 16:39:12 GMT

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!

Re: Proc SQL - lengthy dynamic sql statement in variable

PaigeMiller — Wed, 01 Aug 2018 16:43:43 GMT

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).

Re: Proc SQL - lengthy dynamic sql statement in variable

Urban_Science — Wed, 01 Aug 2018 16:45:24 GMT

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.

Re: Proc SQL - lengthy dynamic sql statement in variable

broth38 — Wed, 01 Aug 2018 16:55:02 GMT

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!

Re: Proc SQL - lengthy dynamic sql statement in variable

Urban_Science — Wed, 01 Aug 2018 17:11:09 GMT

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).

Re: Proc SQL - lengthy dynamic sql statement in variable

PaigeMiller — Wed, 01 Aug 2018 17:17:01 GMT

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.

Re: Proc SQL - lengthy dynamic sql statement in variable

broth38 — Wed, 01 Aug 2018 17:47:12 GMT

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

Re: Proc SQL - lengthy dynamic sql statement in variable

Urban_Science — Wed, 01 Aug 2018 18:02:45 GMT

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).